Databse Design - Same one to many used twice in a table?
Databse Design - Same one to many used twice in a table?
(OP)
Below is the structure of the tables as it is now. Is it good design to have two table attributes/fields use the same relationship?
resOwner and resVendor both refer to orgID it just depends on the orgType. I thought of breaking the Organization table into an Owner and Vendor tables but then how would Address know which table it referred to? foreign keys to both table and only use one or the other?
Organization Table
orgID
orgName
orgType (Vendor | Owner | etc)
Address Table
addID
addStreet
addState
addZip
addType (Billing | HQ | etc)
orgID
Resouce Table
resID
resName
resType
resOwner -> orgID
resVendor -> orgID
resOwner and resVendor both refer to orgID it just depends on the orgType. I thought of breaking the Organization table into an Owner and Vendor tables but then how would Address know which table it referred to? foreign keys to both table and only use one or the other?
Organization Table
orgID
orgName
orgType (Vendor | Owner | etc)
Address Table
addID
addStreet
addState
addZip
addType (Billing | HQ | etc)
orgID
Resouce Table
resID
resName
resType
resOwner -> orgID
resVendor -> orgID
-chris
_______________________________________
RE: Databse Design - Same one to many used twice in a table?
What do you want to do with the res table? What entity does it represent?
RE: Databse Design - Same one to many used twice in a table?
-chris
_______________________________________
RE: Databse Design - Same one to many used twice in a table?
Like this
CODE
----------
VendorID PK
VendorName
...Other Vendor related attributes
Owner
----------
OwnerID PK
OwnerName
...Other Owner related attributes
Address
----------
addID
addStreet
addState
addZip
addType (Billing | HQ | etc)
VendorAddress
----------
VendorID
addID
OwnerAddress
----------
OwnerID
addID
Resource
----------
resID
resName
resType
resOwner -> OwnerID
resVendor -> VendorID
RE: Databse Design - Same one to many used twice in a table?
Lion Crest Software Services
Anthony L. Testi
President
www.LionCrestSoftwareServices.com
RE: Databse Design - Same one to many used twice in a table?
I think RiverGuy is going in the right direction. It makes sense to have Resource-Owner table and Resouce-Vendor table. then they could refer to the same orgID. Similar to his example with the addresses.
-chris
_______________________________________
RE: Databse Design - Same one to many used twice in a table?
--------------------------------------------------
"Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month." --Wernher von Braun
--------------------------------------------------
RE: Databse Design - Same one to many used twice in a table?
Of course a software vendor is a software vendor, but it can (and should) also own hardware to be able to produce software.
So I'd put the references of resources to organizations in a seperate table:
Organization Table
orgID
orgName
orgMainType ->otpID
Organization Types
otpID
otpType (Vendor | Owner | etc)
Address Table
addID
addStreet
addState
addZip
addType (Billing | HQ | etc)
orgID
Resouce Table
resID
resName
resType
ResourceOrganizations
rsoID
rsoResID ->resID
rsoOrgID ->orgID
rsoOtpID ->otpID
The ResourceOrganizations table then maintains all the references towards organizations involved with the resource, which can be more than vendor and owner. There can be the disposer, the shop it came from or it's repaired at etc.
Even if you just and always maintain exactly two relationships to an owner and a vendor this is possible this way. And I introduced a mainType in the Organization table to specify the main type of an organization, eg perhaps in your case Microsoft mainly is a vendor of software you own and so it's the main organization role.
Bye, Olaf.