How do you handle small sets of data?

Question

With really small sets of data, the policy where I work is generally to stick them into text files, but in my experience this can be a development headache. Data generally comes from the database and when it doesn't, the process involved in setting it/storing it is generally hidden in the code. With the database you can generally see all the data available to you and the ways with which it relates to other data.

Sometimes for really small sets of data I just store them in an internal data structure in the code (like A Perl hash) but then when a change is needed, it's in the hands of a developer.

So how do you handle small sets of infrequently changed data? Do you have set criteria of when to use a database table or a text file or..?

I'm tempted to just use a database table for absolutely everything but I'm not sure if there are any implications to this.

Edit: For context:

I've been asked to put a new contact form on the website for a handful of companies, with more to be added occasionally in the future. Except, companies don't have contact email addresses.. the users inside these companies do (as they post jobs through their own accounts). Now though, we want a "speculative application" type functionality and the form needs an email address to send these applications to. But we also don't want to put an email address as a property in the form or else spammers can just use it as an open email gateway. So clearly, we need an ID -> contact_email type relationship with companies.

SO, I can either add a column to a table with millions of rows which will be used, literally, about 20 times OR create a new table that at most is going to hold about 20 rows. Typically how we handle this in the past is just to create a nasty text file and read it from there. But this creates maintenance nightmares and these text files are frequently looked over when data that they depend on changes. Perhaps this is a fault with the process, but I'm just interested in hearing views on this.

Answer 1

Put it in the database. If it changes infrequently, cache it in your middle tier.

Answer 2

The example that springs to mind immediately is what is appropriate to have stored as an enumeration and what is appropriate to have stored in a "lookup" database table.

I tend to "draw the line" with the rule that if it will result in a column in the database containing a "magic number" that maps to an enumeration value, then the enumeration should really exist as a lookup table. If it's unrelated to the data stored in the database (eg. Application configuration data rather than user generated data), then it's an enumeration all the way.

Answer 3

Surely it depends on the user of the software tool you've developed to consume the set of data, regardless of size?

It might just be that they know Excel, so your tool would have to parse a .csv file that they create.

If it's written for the developers, then who cares what you use. I'm not a fan of cluttering databases with minor or transient data however.

Answer 4

We have a standard config file format (key:value) and a class to handle it. We just use that on all projects. Mostly we're just setting persistent properties for our applications (mobile phone development) so that's an appropriate thing to do. YMMV

Answer 5

In cases where the program accesses a database, I'll store everything in there: easier for backup and moving data around.

For small programs without database access I store my data in the .net settings, which are stored in an xml file - of course this is a feature of c#, so it might not apply to you.

Anyway, I make sure to store all data in one place. Usually a database.

Answer 6

If these are small config-like data, i use some simple and common format. ini, json and yaml are usually ok. Java and .NET fans also like XML. in short, use something that you can easily read to an in-memory object and forget about it.

Answer 7

I would add it to the database in the main table:

1. Backup and recovery (you do want to recover this text file, right?)
2. Adhoc querying (since you can do it will a SQL tool and join it to the other database data)
3. If the database column is empty the store requirements for it should be minimal (nothing if it's a NULL column at the end of the table in Oracle)
4. It will be easier if you want to have multiple application servers as you will not need to keep multiple copies of some extra config file around
5. Putting it into a little child table only complicates the design without giving any real benefits

You may well already be going to that same row in the database as part of your processing anyway, so performance is not likely to be a problem. If you are not, you could cache it in memory.

Answer 8

Have you considered sqlite ? It's file-based, which addresses your feeling that "just a file might do" (zero configuration), but it's a perfectly good database and scales remarkably well. It supports a number of APIs and there are numerous front ends for administering it.