Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Relationship Problem

    This is my first post on the forum. I've had some basic experience with database design, but I've run into an issue I need help wrapping my head around.

    Here it is:
    I have constructions projects (Projects table) that have a customer. Sometimes that customer is an individual (John Smith) and sometimes the customer is a company (Acme Property Management). For each project there is only 1 project holder- a person or a company. The distinction is important to me.

    Originally I thought the only way might be to do this programatically and make a system. For example, all company_id fields are 1-10000, and all person_id fields are 10001 - ?. Then if the project's project_holder_id field is 1-10000 I know to look in the Companies table instead of the Persons table.

    I'd like to handle this more elegantly and without having to rely on programming. Any thoughts? Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do a search on supertype/subtype

    the supertype table would be customers, and the subtype tables would be individuals and companies, each with a foreign key primary key back to the customers table

    then the customers table participates in relationships with projects, etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    3
    I like the title of the post ..almost made me think this forum as agony aunt forum :P

  4. #4
    Join Date
    Nov 2011
    Posts
    2
    @r937:

    Thanks for your help. The "supertype/subtype" search helped. I found a very useful the link you posted in another forum in case anyone else needs help on this subject.

    http://www.cbe.wwu.edu/misclasses/mi...persubtype.ppt

    @anyone:

    For clarification.... how does the supertype_id extend through the heirarchy?

    I know I need a PK for the supertype, such as Entity. Do the subtypes of Person and Company simply have FKs of person_entity_id and company_entity_id that also act as PKs for their own subtypes, such as employee_person_entity_id, etc.?

    Thanks.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by johnnyrw View Post
    I know I need a PK for the supertype, such as Entity. Do the subtypes of Person and Company simply have FKs of person_entity_id and company_entity_id that also act as PKs for their own subtypes, such as employee_person_entity_id, etc.?
    yes, that's it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2011
    Posts
    1

    Question related to this issue...

    Thanks for the link to the powerpoint presentation file.
    It was quite useful, but I have a question related to this.
    I have trouble translating an ER diagram to a DB model.

    In the powerpoint presentation there is a slide with an Employee supertype with an Employee Type attribute and subtypes Employee Types themselves (Hourly, Salaried and Consultant), which is very similar to my design situation.

    Suppose I wanted to somehow indicate if a Consultant is the Boss of the Hourly and/or Salaried Employee (either, none or both), how could that be designed in a database model?
    I can put a relationship between them, but keep thinking that might not be the wisest solution....but I'm not sure why (integrity issues?)

    Thanks in advance for any help.
    Last edited by TomLee; 12-16-11 at 18:06.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •