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!
Phew! for a moment I had a fear you were going to defend "intelligent"
keys. I once worked for a company, where my employee number was 927448.
92 stood for Holland/XXXX division, 7 for indirect (white collar), 4
for male, married, 48 for the number of times per week I .. - no, not
really. But you get the picture.
Guess what needed to be done to all systems when a Danish bloke
(4300...) was transferred to our division?
Never, ever use intelligent keys - my IT mentor hammered that in.
You will always, always (need I say it once more?) end up in a
situation not covered. See above example.
BTW, this was a multi national, so growth, expected or not, was
not an excuse.
--
Regards, Frank van Bortel
Received on Thu May 15 2003 - 14:14:51 CDT
