Re: Bill of materials / Groups in Groups

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?

Getting back to the employee/boss database... Consider the standard formulation, where the Employee table has a Boss attribute. The only difference between this and my example above is that now the primary key and the foreign key occur in the same table, and thus you might want to invoke a transitive closure operation, which isn't provided by most DBMSs. So the formulation isn't completely satisfactory. But I don't understand why you say it's not normalized.

Your suggestion -- storing lft and rgt attributes based on a tree walk -- is clever. It allows users to formulate all the queries they want using standard SQL, without needing a transitive closure operation. I suspect you really want the lft and rgt attributes to have a continuous datatype such as float or fraction, rather than integer. If you use integers, then adding a new node to the tree may require a complex and expensive series of cascaded changes to the rest of the tree. This would be a serious violation of the intuitive idea of "normalization" that you seem to be working with. But with fractions you can probably avoid this difficulty. Received on Tue Jan 11 2000 - 00:00:00 CET