Re: 3NF question

"jonnie" <jsavell_at_gmail.com> wrote in message news:1105425237.714101.310640_at_f14g2000cwb.googlegroups.com...

> Yes. I got raked over hot coals about this. It is not really the name
> ID so much as the concept of an ID (which is independent of login) that
> I want.

It's not clear what the concept is.

Let me offer yet another way to look at this question. Does the value "12345" in the ID column refer to the same real world "thing", regardless of whicch table it occurs in?

Let's say we have two tables, "employees" and "trucks". Let's say each has a column marked "ID". Let's say that the value "12345" appears in each table. Does that mean that the employee with ID 12345 is a truck?

There are several ways to deal with this question:

First, there's the classical way, already illustrated by Joe Celko. If you name the columns "EMPLOYEE_ID" and "TRUCK_ID", the situation can't happen.

Second, there's the "single universal ID generator" concept. Basically, each time it's necessary to construct a new ID value out of thin air, we consult the same sequence generator. This sequence generator never generates the same sequence number twice. Using this mechanism, the question asked above can't happen, because the sequence generator will never generate the same value for coulmn EMPLOYEES.ID and for the column TRUCKS.ID .

Third, there's the "each ID has its own generator" concept. In this concept, EMPLOYEES.ID and TRUCKS.ID can each have a value of 12345, but it's mere coincidence. It's as if your 9 digit zip code and my 9 digit social security number were the same. There is no meaning in that coincidence. The question gets raised, how does the user of the data know not to join employees and trucks on the ID column?

There are two separate answers to that question, but I'll stop for now. Received on Wed Jan 12 2005 - 15:36:46 CET