Re: Which normal form is this violating?
Date: 26 Apr 2002 08:06:45 -0700
Message-ID: <c0d87ec0.0204260706.2b886280_at_posting.google.com>
>> 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
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 - 17:06:45 CEST