in reply to Re^2: OT: benefits of database normalization
in thread OT: benefits of database normalization

Actually, I wouldn't put that information in the second table, but in the first:

Design 3a

fact_id fact                   orig_name_id
------- ---------------------- ------------
1       foo is a fooity foo    1
2       bar is a barrish baaar 2

name_id name fact_id
------- ---- -------
1       foo  1
2       bar  2
3       foog 1

If you then say orig_name_id must be unique and not null and must reference an existing name_id, the DBMS will automatically check data integrity for you instead of you having to code an extra function to make sure you don't have too few or too many name_ids that claim to be the original.

That is one of the main reasons you would want to normalize your data structure: To be able to utilize the DBMS's built-in functions to ensure data integrity.

  • Comment on Re^3: OT: benefits of database normalization