February 23, 2009 at 4:15 pm
I have a database with the following four entities: Distributor, Installer, Hardware, Owners. Installers, Hardware, and Owners all need to be identified with one and only one distributor. So far, so good. However, Hardware can have one or no Owners and one or no Installers. The question is how to be design the capability of knowing the distributor of the owner, installer, or hardware without designing a circular relationship into the tables?
February 23, 2009 at 4:52 pm
There's no reason that I know of that this should lead to a circular relationship. Try something like this:
CREATE Table Owners(
OwnerID int identity primary key
, DistributorID int);
CREATE Table Installers(
InstallerID int identity primary key
, DistributorID int);
CREATE Table Hardware(
HardwareID int identity primary key
, DistributorID int
, OwnerID int null
, InstallerID int null);
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 5:35 pm
The design you propose is the one I want to implement. I should have said not that the relationship is circular but that it appears to be a triadic redundancy. It's triadic because there are two ways to go from (say) the hardware table to the distributor table: Directly and via the installer table, which is possible if the hardware has an installer.
I'm guessing the model you propose is the simplest to implement even though it does not get rid of all possible redundancy. And since I am placing a high priority on knowing the distributor for each of the three entities (owners, installers, hardware) I have no alternatives.
February 23, 2009 at 7:44 pm
A circular relationship is one that loops back on itself, like this:
CREATE Table Chickens(
ChickenID int identity primary key
, EggID int);
CREATE Table Eggs(
EggID int identity primary key
, ChickenID int);
So what you have isn't a circular relationship, you just have alternate paths. Why do you that that would be a problem?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 7:56 pm
I should have said not that the relationship is circular but that it appears to be a triadic redundancy. It's triadic because there are two ways to go from (say) the hardware table to the distributor table: Directly and via the installer table, which is possible if the hardware has an installer.
I'm guessing the model you propose is the simplest to implement even though it does not get rid of all possible redundancy. And since I am placing a high priority on knowing the distributor for each of the three entities (owners, installers, hardware) I have no alternatives.
February 25, 2009 at 12:56 pm
Use two tables for hardware, one for each of the two sub-types. I'm not clear whether you actually need DistributorID in these tables. Isn't that implied by the Owner or Installer? There needn't be any redundancy as far as I can see.
CREATE TABLE HardwareOwner (
HardwareID INT PRIMARY KEY
, DistributorID INT NOT NULL REFERENCES Distributors (DistributorID)
, OwnerID INT NOT NULL REFERENCES Owners (OwnerID));
CREATE TABLE HardwareInstaller (
HardwareID INT PRIMARY KEY
, DistributorID INT NOT NULL REFERENCES Distributors (DistributorID)
, InstallerID INT NOT NULL REFERENCES Installers (InstallerID));
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply