I have a source system where all tables are defined with the following "best-practice" structure:
ID number PrimaryKey
STATUS number PrimaryKey (1 = valid, 2 = modified, 3 = deleted)
MOD_DT date PrimaryKey
CODE varchar BusinessKey
col.N ... descriptive data
col.N+1 ... descriptive data...
- the real PK of the table is the first 3 columns
- but all foreign keys refer to the ID column only! (supposedly to the row where STATUS = 1)
- there are several rows for the same ID where STATUS = 2 (modified, not valid anymore) with different MOD_DT (modification date&time)
How would you model this?
- HUB with the ID or HUB with CODE (real BK) or two HUBs (with a same-as-link)?
- SAT multi-active?