Databse Design - Same one to many used twice in a table? 1

[chris5g]

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


-chris
_______________________________________

 

[BigRed1212]

I think all you need is ResOrgId. You have already identified the org type (owner, vendor) in the org table. to duplicate whether the org was an owner or vendor in the res table would seem a step in the wrong direction.

What do you want to do with the res table? What entity does it represent?

 

[chris5g]

Thanks for the fast reply! A resource is generally a piece of equipment. I want to identify who it belongs to (owner) and who we got it from (vendor). That was the logic when the resource table was setup.



-chris
_______________________________________

 

[RiverGuy]

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?

Like this

Vendor
----------
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

 

[MrDataGuy]

I did not completly understand the structure (May be perfect just did not take the time to understand it) but could the own change? Does your structure capture the history? e.g Owner A had it for the first year, owner B for the 2nd. Does it matter, e.g. just the current owner is important.

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[chris5g]

Only the current owner is important.

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
_______________________________________

 

[ousoonerjoe]

River has a great setup. I would only add an "EffectiveDate" and make the table "Resource" be an INSERT rather than UPDATE. Then your SELECT would only need to look for MAX(EffectiveDate). That would also give you an effective tool for history tracking and would allow reporting of your resource usage (prime candidate for a pie chart. We all know how managers like those pie charts).

--------------------------------------------------
“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
--------------------------------------------------

 

[Olaf Doschke]

I wouldn't break up owners and vendors. I'd say whether it's a reference to a vendor or owner is an attribute of the reference itself. It may be seldom in your situation, that an organization is a vendor and an owner, but generally organizations produce somethings and own things of produced by other organizations. While you only put into your database what you own, the concept of organization types is only reflecting your view.

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.