Store static data in an array, or in a database?

Question

We always have some static data which can be stored in a file as an array or stored in a database table in our web based project. So which one should be preferred?

In my opinion, arrays have some advantages:

1. More flexible (it can be any structure, which specifies a really complex relation)
2. Better performance (it will be loaded in memory, which will have better read/write performance compared with a database's I/O operations)

But my colleague argued that he preferred DB approach, since it can keep a uniform data persistence interface, and be more flexible.

So which should be preferred? Or how can we choose? Or we should prefer one in some scenario and another in other scenarios? what are the scenarios?

EDIT:

Let me clarify something. Truly just as Benjamin made the change to the title, the data we want to store in an array(file) won't change so frequently, which means the code won't change the value of the array in the runtime. If the data change very frequently I will use DB undoubtedly. That's why I made such a post.

And sometimes it's hard to store some really complex relations like:

Task = {
  "1" : {
    "name" : "xx",
    "requirement" : {
          "level" : 5,
          "money" : 100,
     }
   ...
 }

Just like the above code sample(a python dict or you can think it as an array), the requirement field is hard to store in DB(store a structure like pickled object directly in DB? not so good I think). So in such condition, I will prefer arrays.

So what's your idea? In such scenario, we should prefer arrays to DB, right?

Regards.

Answer 1

Lets be pragmatic/objetive:

• Do you write to your data on runtime? Yes: Db, No: File
• Do you update your data more than once per week? Yes: Db, No: File
• It's a pain to release an updated data file? Yes: Db, No: File,
• Do you read that data often? Yes: File/Cache, No: Db
• It is a pain to update that data file and you need extra tools? Yes: db, No: File

For sure I've forgotten other points, but I guess the basics are there.

Answer 2

If the data doesn't change very oftern, and your programming in Java, why not use Spring to hold the values?

They can be injected into your bean, and changed easly.

but thats if you'r developing in Java.

Answer 3

Your colleague is correct, BUT there's where you need to put aside the comp sci textbook and be pragmatic. How often will you be accessing this data from your application? If it's fairly frequently then don't incur the costs of access overhead. Instead of reading from a flat file you could still gain the advantages of a db, but use a caching strategy in your application. Depending on your development language you could look at something like memcache or jtreecache.

Answer 4

Yeah I agree with your implied assessment that databases are overused and basic flat files may work in multitude of scenarios. If your application is read-only (and writes are done by the admin when app restarts) I would definitely go with the file. Even if application writes to the file, but only in append mode (vs random inserts/updates) in one thread, I would also use file. Anything else -- need a real database with random updates, queries, concurrency control etc.

Answer 5

It depends on what kind of data you are looking at, and whether or not it needs to be updated regularly.

I tend to keep most things (non-config data) in the database, even if the data isn't going to be repeating (e.g. thosands of rows). Databases will scale so much easier than a flat file, if your system starts to grow fast your flat file might become a burden to your system.

Answer 6

The "flexiable" array in a file is fraught with a zillion issues already delt with by using a DB. Unless you can prove that the DB is really going to way slower than using the other approach use a DB. Move on and start solving business problems.

Edit

Comment from OP asks what the issues with using a file might be, here are a handful (pause to take a deep breath).

• Concurrency: You have to manage the situation where multiple requests may be trying to write back to the file. Not too hard but it becomes a bottleneck.
• Performance: Yes modifying an in-memory array is quicker but how do you determine how much and when the array needs to be persisted to a file. Note that using a DB doesn't pre-clude the use of an appropriate in-memory cache. Writing a file back each time a small modification is made isn't going to perform that well.
• Scalability: Really a function of the first two. In order to acheive any scalable goals you need to be able to quickly modify small bits of the data that is persisted. IWO if you don't use a DB you would end up writing one. If you find you need more than one webserver to support growing demand where are you going to store the file(s)? Now you've got file I/O over a network (ableit likely a very quick one).
• Structure: Your code will be responsible for managing the structure of data, querying it etc if you use an array. How will you do that in way which acheives greater "flexibility" than using a DB? All manner of choices and complexity are needed here.
• Reliability: You need to ensure the integrity of your persisted data. In the event of some failure your array/file code would need to ensure that data is at least not so corrupt that the application can continue.