Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: Multiple Possible Parents

    This is a db design question:

    I am creating a db of projects that contains a table of users/people, companies and projects where

    a user can be a child of the company table and and a project is a child of either a user or a company.

    This clearly does not work in practical terms but I cannot see how to solve this, do I need some sort of intermediate table to link it all ?

  2. #2
    Join Date
    Jan 2004
    Posts
    51

    Re: Multiple Possible Parents

    Originally posted by Bob-A-Job
    This is a db design question:

    I am creating a db of projects that contains a table of users/people, companies and projects where

    a user can be a child of the company table and and a project is a child of either a user or a company.

    This clearly does not work in practical terms but I cannot see how to solve this, do I need some sort of intermediate table to link it all ?
    How about following?

    User (user_id,...company_id,..)
    Company(company_id,..)
    project(project_id,..., parent_id, parent_type)

    parent_type is indicator which will store e.g. 'C' for company , 'U' for user

  3. #3
    Join Date
    Jan 2004
    Posts
    5

    Re: Multiple Possible Parents

    Originally posted by sushant
    How about following?

    User (user_id,...company_id,..)
    Company(company_id,..)
    project(project_id,..., parent_id, parent_type)

    parent_type is indicator which will store e.g. 'C' for company , 'U' for user

    I get where you are coming from sushant but how would you then retrieve the data

    I had thought about doing:

    SELECT * FROM user,company WHERE user_id = ? OR company_id = ?

    But it is then possible, in fact likely that the query would return two records only one of which would be correct as there is no constraint between the id's of the user and company tables making the id's unique

  4. #4
    Join Date
    Jan 2004
    Posts
    51

    Re: Multiple Possible Parents

    Originally posted by Bob-A-Job
    I get where you are coming from sushant but how would you then retrieve the data

    I had thought about doing:

    SELECT * FROM user,company WHERE user_id = ? OR company_id = ?

    But it is then possible, in fact likely that the query would return two records only one of which would be correct as there is no constraint between the id's of the user and company tables making the id's unique
    What should be the output of the query?

  5. #5
    Join Date
    Jan 2004
    Posts
    5

    Re: Multiple Possible Parents

    Originally posted by sushant
    What should be the output of the query?
    I want to be able to get the id and name of the owner of the project and also which table it came from.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if entity C is a child of either parent P1 or parent P2, then P1 and P2 are instances of a subtype of some larger supertype SP

    the supertype SP will have a primary key, which both P1 and P2 will carry as foreign keys

    C will then also carry the foreign key to the supertype SP

    thus you can left outer join C to both P1 and P2 and be assured that you will get only one of them

    well, that's the theory, anyway

    in this particular example, with P1 actually being a child of P2 (which raises the thorny side issue of whether you can have the same userid in different companies), i would just put a two foreign keys to userid and companyid, into the project table

    "parent type" column is not required, since whenever you retrieve a project, you will probably want to do a double outer join anyway -- if it's owned by a user, you'll want that user's company info anyway, and it it's owned by a company, the userid foreign ket in the project table will be null, so you will only get company
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Posts
    5
    Thanks Rudy,

    So basically are you moving what would be the PK's of the P1 and P2 tables in to a parent table in order to keep them unique?

    The affect of this being when you come to insert a new record into either P1 or P2 you would first create a new record in SP get the new identity created and insert that into your new P1 or P2 record as a FK?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's right, that would be the way, using a supertype table

    but like i said, in your example, i might not need it

    companies
    11 acme widgets
    12 gizmos-r-us
    13 thingamabobs inc

    users
    937 11 curly
    936 11 larry
    935 11 moe
    931 13 tom
    932 13 dick
    933 -- harry

    projects
    xp3 --- 11 someacmeproject
    xp3 937 11 acmeprjectownedbycurly
    abc 933 -- harryprojectnotrelatedtoacompany


    since a join from users to companies (going "up") is one-to-one, you can join the project to both users and companies

    for a bit of consistency checking, you might want to declare a constraint that if the user fk in projects is not null, then the userid/companyid pair must exist in the users table, i.e. you can't have a project for a user at a company other than that user's company
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2004
    Posts
    5

    Smile

    Thanks y'all, I think I've got a grip on this now

Posting Permissions

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