0

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, state and county. The state and 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?

Improve this question

1 Answer 1

Reset to default
1

I don't think you should ever use external data "as-is" as a permanent part of your application. As you say every time you reload the table it may be replaced and the fks may change. It is much better to treat the imported tables as "staging" tables and use them only for updating your permanent tables with updated or new records. You would join them using "natural" keys such as state codes. This way you can have your extra columns and only update the columns that contain imported data.

Basic Process:

  1. Nuke State_Imported_From_TIGER_table
  2. Import data into State_Imported_From_TIGER_table
  3. Insert new records into permanent State_Table LEFT JOIN on State Code WHERE State_Table IS NULL
  4. Update existing records into permanent State_Table INNER JOIN on State Code
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.