database design for tradition AND ini-file-like data with integrity

Question

I would like some viewpoints on the best way to handle situations where you have normal (traditional) database data and at the same time also have data that is more like "ini file" data and you want to maintain integrity between them (heirachical data?).

For example, suppose I have a "CURRENCY" table with 100 rows. At the same time I have a system setting for 'default currency' and this has to map to one of those 100 rows.

Right now I would store the default currency code in an XML config file, an ini file, or the Windows registry. This is fine for the data but its a bit of work to make sure that the values in the XML file do in fact correspond to valid foreign key values in the database.

How could this be done (declaratively?) in a database so that the heirachical data can reference and therefore have integrity with the relational data?

Answer 1

It might make sense to store the value by name in your config file, rather than using a foreign key ID. The config file might have "USD", then your code would do a lookup by name to get the row in the CURRENCY table.

Answer 2

It cannot be done, because the configuration setting can always be updated by an external program such as a text editor, regedit etc. The only way to prevent this would be to have a service running that continually monitored the file or registry settings and disallowed any changes unless the database was running and an integrity check could also be performed. This is obviously overkill - the correct solution is to store the configuration data in the database.

Answer 3

It may be that a table with 2 columns, a key (or name) and a value column does what you require. Put your relational data into normalised tables and the 'config' type data into the the key/value pair table.