Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

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:

orgType (Vendor | Owner | etc)
Address Table:

addType (Billing | HQ | etc)
Resource Table:

resOwner -> orgID
resVendor -> orgID

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

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.

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

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 Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

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:

TypeID  TypeOfOrg
1       Vendor
2       Owner
3       Vendor/Owner
4       Other 

No need for Resource Table (?)

The same goes for addType:

TypeID  TypeOfWhatever
1       Billing
2       HQ
3       etc 

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

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close