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?
- Hard-code the descriptions in your code. Use this to look up the SalesAreaId from the database when you need it.
- Hard-code the IDs in your code. Use this to look up the SalesAreaDescription when you need it.
- Add a column to the table for each purpose, e.g. an "IsDefaultOnProductLaunchScreen" column and so on (there could be lots of these).
- 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?
Best Answer
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.