Benefits to using a database table instead of just hard coding a simple list of data if the data is consumed by 1 app.

Question

For a while, I've been told by a number of people that a listing of U.S. States (and territories) should be stored in a database table and cached for applications that use the information. The only reasons they give me for this is to promote normalization and because "it's how we've always done it".

Now if the list changes often because the scope of the application grows internationally (say to include Canadian Provinces), I can understand abstracting the list to a data table that would also indicate a country identifier as well. However, if the list is pretty much locked and only used on 1 screen of the application, is it worth doing the query and caching? Is the difference between storing an SMALLINT foreign key that much better than a CHAR(2)? Is it always practical?

Was just pondering this trend that I've seen with the companies that I've worked with.

Answer 1

You get to use foreign keys in the database.

Answer 2

If you want to attach more information such as state-population or economic output to the primary information then an FK makes this expansion (and subsequent queries) simpler and more extensible

When you can normalize -- Why not!

Answer 3

We might add a 51st state.

Answer 4

Was there really a benefit to use 4 digits for the year in a date? It did not seem likely in this seventies app in COBOL to survive till y2k.

What if Bush's plans to the North American Free Trade Union (NAFTA) and it's currency union with the Amero is not the last step and BC, Ontario etc.become states?

Answer 5

if later you want to add US territories you just insert into the table and tada!

Also as Aiden said if you want more info attached to those records, it will be easier to build off of when its in the DB than if its hardcoded

Answer 6

I say no. If it's a single application that you will be maintaining and you aren't doing anything more complex than filtering a query by state, you can just as soon add/remove states from a hard-coded array. However, if you are doing a lot of complex joins involving states as keys, then you probably would want to put them in a table.

Answer 7

A few reasons you might want to choose the database solution over the hard-coded one.

Extensibility reasons:

• Programmatically adding records (although new states are not very likely to appear within the life-span of your application, but users sometimes have strange needs). This might even be left to the users of your application through a dedicated screen. If the values are hard-coded, you'll have to deliver a new version of your software.
• Adding information to records. For instance, you might be asked to add a user-editable population field.

Integrity reasons:

• Foreign keys will give you integrity constraints: you're sure to never add a record with a wrong state code if you have a foreign key. Note that you might use the codes as keys, which would save you the joins problem.

Still the choice depends on the size, number of users and life-span of your application. The bigger those figures, the more you should consider the database solution.

Answer 8

I put them in the database, for a few reasons:

1. It's just good normalization practice.

2. Why should I hardcode any data in my application if I can avoid that by putting it in the database?

3. Personally, I like the comfort of the guaranteed uniqueness property. When two tables point to the same foreign key, I know that they are referencning the same thing. When two happen to share the same two character code... well, then it's up to the application.

4. Constraint Checks.... lots of two character codes are illegal, but the database won't be able to help you with that. It's a lot harder to have an illegal foreign key entry.

5. Speed. I haven't benchmarked, so I may be wrong, but I bet having the database combine entries for you is faster than you doing it yourself. If you are using the state code for anything else (e.g., to get the non-abbreviated name of the state, or to maintain a list of valid zipcodes for that state ), a join is probably faster than whatever you would write.

Answer 9

I completely agree that it is unlikely enough that the states list will change in our lifetime that you can hard code the list.

But a reason to put it in a database would be so you could link the state abbreviation with the full state name. Then you can use the state abbreviation as the unique key and do joins on it. This would be worthwhile normalization.

I've never heard a compelling reason not to use the state abbreviation as a unique key, even though it goes against "proper" database design. Just like I've never seen a database normalized to the fifth level that had any sort of performance. Although fifth normal form is a neat and educational exercise.