29

Many applications include 'static data': data that doesn't really change during the lifetime of the application. For example, you might have a list of Sales Areas that is likely to be a fixed list for the foreseeable future.

It isn't uncommon to find this static data in a database table (often because you want to refer to it in the foreign keys of other tables). A simple example table will have an Id to use as a primary key and a Description. For example, your SalesArea table will have (at least) a SalesAreaId column and a SalesAreaDescription column.

Now, in code you might not want to treat each row of the table the same. For example, you might want to set a default Sales Area on some screens, provide different figures for some areas, or restrict what users can do in other areas.

What is the best way to refer to this static data in code? Why?

  1. Hard-code the descriptions in your code. Use this to look up the SalesAreaId from the database when you need it.
  2. Hard-code the IDs in your code. Use this to look up the SalesAreaDescription when you need it.
  3. Add a column to the table for each purpose, e.g. an "IsDefaultOnProductLaunchScreen" column and so on (there could be lots of these).
  4. Something else.

Are there any other special considerations I should make when dealing with static database data? For example, giving these tables a special name?

1

9 Answers 9

16

How about load them into a cache (usually implemented as a hash table) when the application starts up? If you do that then you don't even have to query the database (well, not more than once).

I would also suggest avoiding hard-coding anything. Add default indicators (initially in the DB table and also in the cache structure) for screens that need defaults. For doing look-ups on non-defauilts, try to store the keys that will be looked up in a configuration or properties file if you can.

4
  • Caching is good of course, but how do you update these values? Presumably an application restart, or some sort of cache invalidation strategy?
    – Steve
    May 16, 2011 at 9:09
  • 1
    @Steve Yes, exactly. Depends on the application. Restart is good for a something starting frequently. For a long running application, perhaps reloading the cache once a day during slow times. My question would be, what about a scenario where the application runs many very short lived times. Like, perhaps a PHP script or something similiar.
    – tylermac
    Jun 14, 2011 at 18:14
  • 1
    The database will run its own cache for frequently accessed data so you will re-implementing something that is already implemented (and probably not as well!) Aug 1, 2013 at 1:47
  • @JamesAnderson: Caching in the application means there will only ever be one call to the database. Yes, databases will have their own caches but those can be invalidated/refreshed by events outside the control of your application, and you still have to have a connection to the database and make a query to get that data (and hope that it's in the db's cache). It's really not that difficult to implement a simple in-application cache. Aug 1, 2013 at 16:58
7

An alternative to the DB or hard coding is to use a config file read at start-up time. You could then store this data in a read only structure within your code.

IN the rare (but not impossible) case where you edit this data you will have to restart the app. If this is not possible you can write a more complex config manager that checks for changes in the config file every time the data is accessed, this is actually pretty efficient as you only need check a time stamp on the file and then invalidate all the data if the file is updated.

3
  • 1
    Good idea for some types of static data, but not so good if you want to enforce FK relationships as described in the question.
    – Kramii
    May 13, 2011 at 15:21
  • Question did not say this was a requirement, only a scenario. If it is not needed then config file approach works well.
    – Steve
    May 14, 2011 at 21:03
  • You're right, I wasn't clear enough. But I'm pleased... because I've learned something from your answer. I've never come across this approach before.
    – Kramii
    May 15, 2011 at 21:17
4

This is premature optimization at its worse.

Firstly any modern DBMS will retrieve data from small tables at lightning speed and they all have caching algorithms ranging from good to superb (the more you paid for the DBMS the better the cacheing!). So you are optimizing something that consumes minimal resources.

Secondly you have very little experience of real world business applications if you imagine something like a "sales area" is static data. These are liable to change with every change of Marketing Director or CEO. So you are heading for a world of pain two years down the line.

There are only two ways to go here:-

Store it in a database and access the data with "normal" sql.

Store it in a fancy XML configuration file (possibly accessed via REST or SOAP) which can be easily edited whenever there is "a strategic change of policy".

3

If the data relates to existing data in your DB, it's probably as efficient to add it to the DB as it is to add it to code. If it doesn't then I'm usually tempted to "take that bullet once," and put it into code until the first time it changes.

Often what we think will be static turns out not to be, and when that happens, you don't want to have to wait for a code release for a change to go through. As soon as that happens once, put it in the database and write an administrator page to do further updates.

To take your example, if you already have Sales Areas in the DB, add a description there, don't build a hash table to relate the database data to hard-coded lists. But if you don't then build a hash table of Sales Areas by all means, but be ready, the first time someone changes a description or adds a new Sales Area, move it to the DB.

1
  • "Often what we think will be static turns out not to be" - so true.
    – Kramii
    May 13, 2011 at 15:21
3

Why not just hard code everything? The main problem I've always had is referencing static values from the DB in the application code. It's one thing if you're just directly building a drop-down list or something out of static values, but what if some application logic depends on the values from the DB?

In a simple app I currently have a list of edit states for pieces of content: Draft, Published, Archived.

The content items need to be treated differently depending on which state they're in. If I were to keep this state data in the DB, with values 1, 2, 3, respectively, how would I go about checking if something is in Draft state?

if (content.State == 1)
or
if (content.State == "Draft") ?

I've just hard coded the values!
Same thing if you use a cache / hash table: you still have to use some value written in your code as a key to look up your data.

What are the disadvantages to the hard-coding aproach?

2
  • The disadvantage is as pdr said, "Often what we think will be static turns out not to be".
    – tylermac
    Jun 14, 2011 at 18:21
  • 2
    But if you're actually referencing the static data values in the code, you can't change it in the database without breaking the app. For sure it depends on what the data is being used for: as i mentioned above, if it's just populating a UI element so that a user can select a value and have that piped directly back to the DB as part of a record in another table, then the static data in the DB can change independently of the app code. I'm pretty sure that's the situation @pdr is talking about: the app handling the set of static data as a single item.
    – Dave
    Jun 14, 2011 at 18:47
2

Similar to what @FrustratedWithFormsDesigner said, this is typically done with a cache, as it means you only ever have to load the static data once, but it follows the OAOO pattern, meaning we're not defining the data in two places (database and in your code).

I know the the NHibernate ORM offers this functionality through a 2nd level cache. You can tell it to cache data from a certain table, and say that it's read-only. It will be loaded the first time it's required, and won't hit the database again after that, even if you access the data from multiple sessions.

2
  • +1 for Once and Only Once. But what about treating different rows differently?
    – Kramii
    May 13, 2011 at 15:23
  • 1
    @Kramii - You can use something like enumeration classes. If the metadata only related to your program, then I'd put the business logic (IsDefaultOn...) in a property on the entity. Have it return true for the one entity. That would allow you to find that entity, given the whole collection. Or you could use a controller class that will provide you the appropriate entity with a method call. May 13, 2011 at 16:47
1

It depends on what you are doing with the data. If it is a list of something I will usually pull it into an array. If the list needs to grow in a another version it is easy to just add on to the database and change the code to handle the extra data in the array (which may not even be necessary depending on the code, eg listing the data with a for loop using the upper bound of the array). If it is a list of settings, I will usually hard code those as there usually aren't many and is easier and quicker than using an SQL statement. If it is a setting that the user can change and I want to save the selection for subsequent launches I will create a table to use as a registry and just pull individual entries to variables as needed.

1

I know this answer was accepted but I wanted to share how we did this at my last web development shop where we tried to reduce database I/O's as much as possible.

We used server side include files for as many look up type data structures that we could. Mainly this was for site navigation (to include subnavigation) but we also used it for as many drop downs and checkboxes as possible (States, Countries, Categories).

Originally, we pulled all this data from the database. Since we gave the customer an admin widget they could change this data at will and we never got bogged down with little nickle-dime changes. Most of the time this data hardly ever changed but ocassionally it would change.

We were always looking for faster load times. So we decided to implement as many static server side text files as we could. We did this in side the admin widget. Each time a database table was updated we would regenerate the corresponding static text file. This gave us a very flexible and speedy environment.

1

My solution to this, which may not work in all situations, is to bind the static database data to a hard-coded enum. Since the problem comes from having dynamic data (database) bound to static logic (code), make this binding explicit (and loose) by having a database table that associates to the enum. Ex:

LooseDBCodeBinding (database table)
   ID : Int32 (key)
   Name : String
   HardCodedTypeID : Int32

// in code:
public enum LooseDBCodeBinding
{
   TYPE_1 = 1,
   TYPE_2 = 2,
   TYPE_3 = 3 // etc...
}

Then write a UI that allows you to easily view the list of LooseDBCodeBinding records, and map them to LooseDBCodeBinding enum values (including supporting "broken" bindings). You can then program around the enum, and design the database around the table key, and it's just this one table that has knowledge of both contexts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.