Re: Which normal form is this violating?

>> I'm having the usual disputes about database design issues.
 

The information that my coworkers have is say, x and y. It has a one to one relationship. Therefore, it goes into one and the same table.

 CREATE TABLE Table_A
 (fieldx INTEGER NOT NULL PRIMARY KEY,
  fieldy INTEGER NOT NULL);

However, they are arguing that it goes into another table.  

 CREATE TABLE Table_A
 (fieldx INTEGER NOT NULL PRIMARY KEY);

 CREATE TABLE Table_B
 (fieldx INTEGER NOT NULL PRIMARY KEY,
  fieldy INTEGER NOT NULL); <<

Did they put a FOREIGN KEY constraint anywhere?

I would call it a Domain-Key violation because Table_A is redundant. SQL prevents you from having two tables with the same names, but relational theory says that since a table is a set, it can appear only once in a schema, no matter what the name. Table_B represents a set of facts about the relationship between (fieldx, fieldy); Table_A represents a redundant projection of Table_B.

The redundancy leads to errors:

INSERT INTO Table_A VALUES (42);
INSERT INTO Table_B VALUES (42, 13);
INSERT INTO Table_B VALUES (99, 23);

Is 42 an element of the domain of fieldx? Yes and yes, he said redundantly. Is 99 an element of the domain of fieldx? Yes and no, he said contradictingly.

>> (Actually, thye have "designed" a number of strange tables, and
then put views on top of them, to come back to the same one to one relationship. Very strange and complex. )<<

OO programmers? I keep seeing meta-data tables from those guys and columjn names like "foobar_value_type_id", which mean less than nothing. Received on Fri Apr 26 2002 - 10:06:45 CDT