Re: Which normal form is this violating?

--CELKO-- (71062.1056_at_compuserve.com) wrote:
: >> 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.

If there really is a 1-1 relation between x and y, then Table_A does appear redundant.

However, the inherent logic of the two tables is fine. Table_A is a list of *all* the elemets. Table_B list just those elements which have a certain attribute.

: 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.

Redundant only if it is true that there is always that 1-1 relation between fieldx and fieldy.

That redundancy is an implementation detail, based on your knowledge of the data and the planned set of business rules. The logic of a table which lists all the values and a table which relates a (sub)set of those values to other values is not inherently redundant.

: 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.

On the contrary. Table_A defines the domain of fieldx, and Table_B is constrained to only use those values. This is a fairly normal constraint. In other words, fieldx in Table_B is required to be a (possibly complete) subset of the values in Table_A.

Whether this correctly reflects your real world data is certainly a question, though I agree it's a bit unusual for a table to just contain a single list of values with no additional attributes in that table.

Whether this is right or wrong depends entirely on the data to be stored and the "real-world" logic of what it means. Received on Fri Apr 26 2002 - 16:32:05 CDT