Re: Database design question

andyho99_at_yahoo.com said...
> Hi,
>
> We are converting legacy files into Oracle database. Through
> normalization process, we have a parent table and several child tables
> associated with it. Only parent table contains the unique key
> (combination of 5 columns). The child tables have an Oracle sequence
> number associated with parent table but without these 5 columns. This
> worries me a little. If there is anything wrong on the logic to build
> Oracle sequence numbers in the parent and child tables. The records in
> the child tables may never find its parent record.
>
> What's the pro and com of this design? One obvious advantage is space
> saving. What do the most people do for this kind of situation? Thanks.
>

Well, I'll add my two cents. Way back when I was converting some COBOL based flat (master/transaction) files to a database (it happened to be IDMS instead of Oracle, but I think the principle applies), I had the same sort of worries.

In my flat files, I had "real" data ... like the sacrosanct newspaper subscriber's ID number. This subscriber ID contained some built-in intelligence (like whether it was the morning or evening edition, and so on) and had been used by the newspaper since the 1800s. Now they wanted me to convert to a simple sequential number, starting at 100,000. What if someone asked a question that was based on the intelligence built into the "old" ID? What if I forgot to include a column that gave me the flexibility of answering any future question? What if something in the database broke and I had to manually tie all those meaningless sequential numbers together?

Well, to make a long story short: my fears were unfounded. As long as I had a "good" data model that reflected the business, the database was sound. Now that I'm with Oracle, I'll never go back to intelligent keys as a table's primary key. Sequences are good!

-- 
/Karsten
DBA > retired > DBA