Our database is currently bringing in data structures from third-party constructs that we don't entirely control. One major example is TIGER data from the Census bureau, loaded in via the postgis plugin. Some of these tables are, for example,
county tables have lots of info, but we would like to be able to store even more information about states and counties than are loaded in with postgis by default.
One option is to create additional columns on these tables, and store the info in there. However, if we ever update postgis, these tables might get nuked, and all of our additional columns and info will also be vaporized. Yet another option is to have a table of
state_additional_information that holds our additional info about a
state. The relationship will be one-to-one, and the
state_additional_information can have a foreign key relationship to the
state table. Now, if and when the
state table gets nuked, we can just drop the fk constraint, nuke the
state table, and re-establish the fk constraint. (of course, re-mapping the fk will be a pain if the keys have changed around, but I don't think that there's any avoiding that.)
Besides having the potential extra
JOIN, is there any reason to not store my "extra columns" in a new table? E.g., will this create the possibility of data-integrity problems later, or some such?