Re: Which database design is better

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: David Browne (meat_at_hotmail.com)
Date: 10/03/04 

Date: Sun, 3 Oct 2004 16:49:25 -0500

"Steve Kass" <skass@drew.edu> wrote in message
news:OTJM63YqEHA.1960@TK2MSFTNGP10.phx.gbl...
>
>
> Joe Celko wrote:
>
>>>>What's circular about two paths from A to C, and what does this have
>>>>
>>to do with the question? <<
>>
>>Draw a picture if it helps:
>>
>> A --> B --> C
>> |__________^
>>
>>Cycles do not have to be directed to cause problems. And the question
>>was about circular FK references. DB2 and other larger RDBMS systems do
>>more detailed checking for cycles than SQL Server and can allow for more
>>patterns.
> The question was about references that led from A --> B --> A. I would
> call that a circular reference, and I would not call the above picture a
> circular reference.
>>This (A,B,C) was from the first version of DRI in DB2. Both A and C
>>would be fired, and if they hit the same row in C, the results were
>>unpredictable -- it was the last one action that got there in physical
>>time. This was later changed to a rollback situation.
>>
>>>>So when you suggested this recently, did you mean for it to represent
>>>>
>>an entity or a relationship? <<
>>
>>A game is a relationship between two teams. Teams are not attributes of
>>a game entity. A relationship can also have its own attributes, like a
>>marriage (relates husband and wife) having a location, date, minister,
>>license number, etc.
> Marriage can be considered an entity, too - I think you've said that
> yourself. Perhaps a better example is this:
>
> CREATE TABLE Customers
> (cust_nbr INTEGER NOT NULL PRIMARY KEY,
> cust_name VARCHAR(35) NOT NULL,
> street_address VARCHAR(35) NOT NULL,
> city VARCHAR(25) NOT NULL,
> state CHAR(2) NOT NULL, -- should this refer to a state table?
> zip_code CHAR(5) NOT NULL)*;
>
> *If this table represents an entity, then [state] is an attribute and
> cannot be considered a relationship, even though the argument for a
> "resides in" relationship is stronger than the one that views [state] as
> an attribute of a customer? As useful as it is to know clearly what the
> contents of a table represent, the black/white view that tables can't
> model entities and many-to-1 relationships at once is not very helpful.
> In this example, do I need a street-address table, a city table, a state
> table, and a zip_code table?
>
> Can you give a foolproof criterion that allows me to distinguish which
> attributes are actuall many-to-1 relationships and have to leave the
> table?

I couldn't agree more. But I'd take it farther. Conceptually, every
attribute is the one-side of a one to many relationship. A relation is a
subset of the cross product of a number of domains. Each tuple in a
relation is made up of elements from the domains. Some domains are
enumerated the schema, some are not. For instance the domain of Stores is
enumerated inside the schema, the domains of SSN's and Salaries and First
Names are not. Now, every attribute is modeled by column, which has values
belonging to a domain. If the domain is enumerated inside the schema, then
the column modeling the attribute is a forign key to the table enumerating
the domain. If the domain is not enumerated, then no foreign key exists.
But if you simply added an enumeration of the domain to your schema, then
the attribute would be a foreign key to it.

So Store is every bit as much an attirbute an of Employee as Job Title, Zip
Code or Salary.

David