×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

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

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
 

-chris
_______________________________________
 

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

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?

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

(OP)
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
_______________________________________
 

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

Quote:


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

CODE

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

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

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
www.LionCrestSoftwareServices.com

 

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

(OP)
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
_______________________________________
 

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

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

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

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.

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