Database Design - Reusing one-to-many relationship in a table?

[LuciusThorne]

Presented below is the current table structure. Is it considered a good design practice to have two table attributes/fields utilizing the same relationship?

Both resOwner and resVendor attributes correspond to orgID, with the distinction being the orgType. I have contemplated dividing the Organization table into separate Owner and Vendor tables. However, this raises the question of how the Address table would determine which table it references. Should foreign keys be utilized for both tables and only one of them be employed?

Organization Table:

orgID
orgName
orgType (Vendor | Owner | etc)
Address Table:

addID
addStreet
addState
addZip
addType (Billing | HQ | etc)
orgID
Resource Table:

resID
resName
resType
resOwner -> orgID
resVendor -> orgID

 

[mintjulep]

Consider that an organization could be an owner in one transaction and a vendor in another transaction.

orgType is not necessary in the Organization table.

The role of an organization in any transaction is given by resOwner or resVendor.

 

[Mike Lewis]

The answer depends on whether a given organisation can be both an owner and a vendor. If it can, then clearly you don't want the OrgType field. And, in that case, you wouldn't be able to split the table between owners and vendors as that would introduce duplicate records, not to mention introducing a many-to-many relationship between organisations and addresses.

I note also that you describe an OrgType as "(Vendor | Owner | etc)". The "etc" suggests further organisation types are possible, which is another reason for not being able to split the table.

However, if owners and vendors are two distinct entities (an owner can never be a vendor and a vendor can never be an owner), then there would be a case for splitting them into separate tables. You would still have the resOwner and resVendor fields, but they would point to the two different tables. That might still leave the issue of many-to-many addresses, plus it would not cater for the "etc." case noted above.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Andrzejek]

If Organization Table and Address Table has a 1:1 relation, I would just have one table, but I would have another table for orgType:

[pre]
TypeID TypeOfOrg
1 Vendor
2 Owner
3 Vendor/Owner
4 Other[/pre]

No need for Resource Table (?)

The same goes for addType:

[pre]
TypeID TypeOfWhatever
1 Billing
2 HQ
3 etc[/pre]

Obviously you would have to come up with better names for those tables/fields...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

Statement A: "Any organization might act as any type in a 'res'"
Statement B: "An organization will always be only one type."

Statement A will always be true.
Statement B could become false at any time for reasons that you cannot control.

Design for A.

For any 'res" (that I have called a 'transaction' previously), and number of organizations might participate. So design for a 1:many relationship there.