Clear all

Modeling source with (almost) composite PK  

Posts: 9
Active Member
Joined: 10 months ago

Hi all,

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 problem:
- 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?
- other?


TIA, Gabor

2 Replies