Re: Database design question

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Thu, 15 May 2003 04:24:12 GMT
Message-ID: <3EC31A98.2090104@adelphia.net>

Karsten Farrell wrote:
> 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!

Well, to add a different perspective (not that I totally oppose sequence numbers), but there is the danger with them of breaking links. Since they are a pseudo-key, there is no way to be 100% sure just from the SEQ# key that the conversion completed correctly.

What Karsten said about a good data model, is the real key to success. If you (andyho99) use SEQ#'s merely to save space, you are thinking about the problem in the wrong way. Hopefully you have Business Analysts to verify the data integrity after conversion. You do plan to verify the new data don't you?

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Wed May 14 2003 - 23:24:12 CDT