Where to store system values

This seems like it'd be a common question, but I cannot find it. Perhaps "system value" 开发者_开发技巧is the wrong phrase.

Anyway, by "system value" I mean the built-in values that a system has for a given concept. For example, if I have a list of categories (e.g., mexican, american, italian, etc...) where would you store them? Would you hard-code them (maybe an enum) or put in the database?

If you say hard-coded, would your answer change if users could create new categories? Obviously you'd have to store the new ones in the database (or another medium like xml) but would you leave the standard system values hard-coded, and then merge them at run-time?

There are some symbolic values which are true constants for an application (e.g. the value of pi, or the maximum value an integer can represent). Putting them into source code is fine.

If you foresee that a user should need to change the values, you need to put them into an external configuration (or database) file. Having some values in code and some in the configuration is confusing; for lists, it may also be difficult to have the user remove values he does not want.

For values that are in a configuration file, you still want to keep them under source control. For this, the common options are:

1. for a configuration file, have a default version of the configuration file in source control.
2. for SQL data, either have a file with SQL commands or a program creating the initial database under source control.

If users can add, edit or delete values from the list, then obviously hard-coding them in the application is not a viable alternative. However, if users do not control the list, and if the list is relatively short, then hard-coding them (as an enumeration for example) is a perfectly valid choice, even if they have to change fairly often, so long as you have a mechanism in place for easily updating the client application to the latest version. This approach is obviously simpler than storing them in an external data source.

However, I can't think of too many uses for something like a list of nationalities other than in a picklist for data meant to end up in a database, and in this case it makes more sense to pull the list from a table in a database that is bound to other tables by proper relational constraints. I've inherited more than one application that maintained a list of options in two places: a database table and a matching enumeration compiled into the application. This situation is known as "not good".

You answered your own question I think.

If something will never change over the life of your project (good luck with that), then I use an array, or hash. Generally I have a function return it.

But usually I end up storing everything that could possibly change in the database.

If you wish to differentiate the original from the user submitted categories, add a column with a default value of 0, and don't set it on insert. Set your original values to 1.

If a list of values is tied closely to application logic, I generally don't have a problem storing them with the application, as enums or similar. You'll have to update your application with new logic regardless, and storing the values in a database just increases the chances that someone will mistakenly add or remove a value without updating that logic.

The example you've given ("Mexican", "American", "Italian"), though, is a prime candidate for storage in a database. This list seems likely to change frequently, and nothing in the application code needs to be updated to handle new values. If you decide to handle Elbonian food, all you need to do is update the database and you're done. No reason to force an application update if you don't need to.

Other folks have given good answers. I just want to add that the example you give of nationalities is a case where you shouldn't encode them as an ENUM in the database because they're too likely to change.

Use ENUM only for a small set of values that is guaranteed not to change. I.e. it would be logically nonsensical to add or remove values form the set. If your set of values are subject to change, store them in a lookup table so you can change data values simply with UPDATE/INSERT/DELETE commands. Redefining an ENUM is a metadata change and this is generally expensive.

Note that if you think the list of values "probably" won't change, or there's "no reason" for them to change, or any other such qualified rule, you can assume some manager will require the set to change at some point.

PS: ENUM is a database type specific to MySQL. Some other databases can achieve a similar result with a CHECK constraint.

Put your data in the database. Hardcode nothing.

Those values will change, or at least be extended. If they relate in any way to data, do yourself a favor and put them in the database to start with. This will not only save you time later when adding additional values, it also allows them to participate in referential integrity constraints at the database level.

I smiled when I saw the title of your post because my databases almost always include a table called SystemValues which is used to store the "one-off" values that configure the operation of the program, such as (for instance) the root folder in which to store generated output. There's also a matching stored procedure GetSystemValue(Key, ValueIfNull) to retrieve them.

For projects which allow customization to the user level I also include a table called UserValues with an extra UserID column, and a GetUserValue(Key, UserID, ValueIfNull) stored procedure.

For those applications that aren't database oriented, Windows provides you with a nice "free" database — the registry — or you can put together your own configuration files.