Re: Bill of materials / Groups in Groups

On Tue, 11 Jan 2000 10:18:07 -0800, "Harry Chomsky" <harryc_at_chomsky.net> wrote:

>joe_celko_at_my-deja.com wrote in message <85dv34$34t$1_at_nnrp1.deja.com>...
>>This
>>is not a normalized table and you can prove it very quickly. In a
>>normalized databsae, you have one fact, in one place, one time. Change
>>Chuck's id number and you have to change the boss column in three other
>>places.
>
>Now I'm very confused. What definition of normalization applies here? Are
>we talking about 3NF or BCNF or some other mathematical definition? Or is
>it just the intuitive idea that "each fact should be stored in only one
>place"?
>
>Do you consider the following set of tables to be normalized?
>
>CREATE TABLE Customer (
> CustomerID int,
> Name char(30))
>
>CREATE TABLE Product (
> ProductID int,
> Description char(50))
>
>CREATE TABLE Order (
> OrderID int,
> CustomerID int,
> OrderDate date,
> ProductID int,
> Quantity int)
>
>In this database, if I change the ID number of a customer or a product, I
>may have to cascade the changes through many rows of the Order table. So in
>some sense the database fails to "store each fact in only one place". But
>if you conclude from this that the database isn't normalized, then you must
>be using a concept of normalization that I'm unfamiliar with.
>
>Can anyone shed light on my confusion here?
>
I haven't followed this that closely but I'll try. The 'ID' either should be a primary key or an ID but not both, that's the source of confusion.

If it's a primary key then the entity it uniquely identifies should not change, if it's an ID of some sort outside of the system then it should not be a primary key.

I have had a problem *not* using SSN's as PK's because it makes so much sense until you look into it. I would feel foolish except one of the arguments for using SSN's is that the University system where I went to school (and learned database theory and programming) uses SSN as the student ID. It's a bad idea even if it's used on huge systems.

Out in the real world the issue came up again bringing up a HR system, and the ultimate answer was that PK's and ID's should not, as in absolutely not, be the same.

One of the best sources of information on this topic was the generous readers of this group.

The PK should be specific to the database and meaningless outside of the database. Then it should be impossible for factors outside the database to force a change to the PK (and therefore impact the relational structure) within the database.

hth;

DCraig. Received on Tue Jan 11 2000 - 00:00:00 CET