Re: Database design question

From: Andrew <andyho99_at_yahoo.com>
Date: 28 May 2003 08:39:08 -0700
Message-ID: <8882aa3c.0305280739.e6c55b4@posting.google.com>

Frank <fvanbortel_at_netscape.net> wrote in message news:<3EC3E72B.3030903_at_netscape.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!
>
> 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.

excerpt from one of the response:

---

Also as part of your conversion you could generate a third table as a safety
check, which contains both your sequence and compound key. If you ever need
to prove your results you have the clear building blocks to do it. You could build it and archive it, or build it in MSaccess if you need to...

---

Just like picking stocks, I've got for and against votes from various experts. I think I'll take your advice for bullet proof vest. Anyway, I have to load the data to staging tables, whcih contain compound key. I'll keep these staging tables. Thanks. Received on Wed May 28 2003 - 10:39:08 CDT