Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40

    Multiple foreign keys from same table

    Hey all.

    I'm designing a data model for a system I shall be designing.
    I have a model and I know it will work, but I was wondering if what I have done is considered to be 'good practice'.

    I have a table of users and another table of projects
    For each project is assigned a primary, secondary and 'shadow' supervisor.
    The way I currently model this relationship is to have all 3 supervisors mapped as the user id of the appropriate person as a foreign key from the users table.

    So effectively I have
    -----------------------------------------
    [Table]User
    [PK] User ID
    User Name
    -----------------------------------------
    [Table]Project
    [PK]Project ID
    Project Description
    Project Date
    [FK]Primary (Mapped against User ID)
    [FK]Secondary (Mapped against User ID)
    [FK]Shadow (Mapped against User ID)
    -----------------------------------------
    Each user can be associated with many projects but can only have one role on that project

    Is it considered good practice to do this?
    I wouldn't know what affects this 'might' have on the database performance or efficiency. My arguement for this method is that because the user can only have one role on any given project that their is no redundancy in this model.

    But i'm pretty new to database design so would glady listen to other people's views

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You design is acceptable if the primary/secondary/shadow threesome are "set in stone". It would however require the addition of a check constraint to enforce that "the user can only have one role on any given project", something like:

    CHECK (primary != secondary and primary != shadow and secondary != shadow)

    A more flexible (i.e. adaptable to future changes) model would be:

    [Table]User
    [PK] User ID
    User Name
    -----------------------------------------
    [Table]Project
    [PK]Project ID
    Project Description
    Project Date
    -----------------------------------------
    [Table]Project_User
    [PK]Project ID [FK}
    [PK]User ID [FK]
    Type (check in (Primary,Secondary,Shadow))

    This model ensures that each User ony has one role on the Project through the PK constraint, and allows for the addition of a new role type ("Tertiary" perhaps) in the future.

  3. #3
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    Thanks a lot for your response, it raised an issue i'd nvr thought about (flexibility).
    I wouldn't know how to put a 'CHECK' on a database - but this is all handled in code in my application as this is merely a back end to a web applciation i'm writing.

    I've never used split Primary Key's in a table before - because i've only ever written a database as a backend to fairly simple web applications i've always just used a bog standard many 2 many relationship.
    So your refined model would look somethign like.......
    [Table]User
    [PK] User ID
    User Name
    -----------------------------------------
    [Table]Project
    [PK]Project ID
    Project Description
    Project Date
    -----------------------------------------
    [Table]Project_User
    [PK]LineID
    Project ID [FK]
    User ID [FK]
    Type (check in (Primary,Secondary,Shadow))

    Using that model i've always used application code to check the constraints the data

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Superfly1611
    [Table]Project_User
    [PK]LineID
    Project ID [FK]
    User ID [FK]
    Type (check in (Primary,Secondary,Shadow))

    Using that model i've always used application code to check the constraints the data
    you would not need LineID, and in fact, if you did use it as the primary key, you would need an additional UNIQUE constraint on the pair of columns ProjectID and UserID to ensure that a given user can be linked to a given project at most once

    that's one of the benefits of having them as a compound primary key, the primary key is unique by definition

    also, the CHECK constraint on the Type column is usually handled by application logic anyway, e.g. from a dropdown list, the CHECK constraint ensures that you can't enter an invalid Type through SQL

    many databases don't support CHECK constraints

    (you didn't say which one you're using, or if you did, i missed it)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by Superfly1611
    I wouldn't know how to put a 'CHECK' on a database - but this is all handled in code in my application as this is merely a back end to a web applciation i'm writing.
    Wrong perspective: your web application is merely the (current) front end to the database you are designing.

  6. #6
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    Thanks for your responses - i've got my model sorted now, and i've also used composite keys through the rest of my database where applcable to refine it further.

    Just to let you know the weapon of choice for my project was SQL Server 2000 don't know if that's capable of 'CHECK' or not.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, sql server supports CHECK constraints
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2004
    Posts
    54

    Your Design Will Break Shortly

    Hi,
    I can assure you.... as the business will change, your design will break.
    The problem is your business entity is a 'role'. Let me explain like this. A 'role' is a party or responsibility some plays or has. EVERY SINGLE database you ever design that has any roles, should be designed so that you have -SEPARATELY - a person, and the roles they play.

    The reality of ANY BUSINESS is that one single person can and will over time play more than one role. This assures, that no matter what new 'role' the business asks for tomorrow is already built into your design. AND THEY WILL, trust me.

    Many database designers just don't get this simple concept.

    But if you ask yourself this question: Can a 'primary' supervisor ever become a 'secondary' supervisor? .... or can a seconadry supervisor ever become a shadow supervisor, or.. etc. OF COURSE THEY CAN. A person can and will (sometimes over time, sometimes at the same time) play many roles.

    OK...sorry. I'm just trying to help see a basic concept that save you years of re-work when the next role comes along.

    So create a separate entity for a 'person', and a separate entity for 'roles'. Then when you related a person to any other business entity, you drag their id, and their role id together.

    Vmusic

  9. #9
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    I will keep that in mind
    Thanks for your input Vmusic

  10. #10
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Vmusic,

    Can you please talk louder? I can't hear you.

    Ravi

  11. #11
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40

    Smile

    Quote Originally Posted by Vmusic
    But if you ask yourself this question: Can a 'primary' supervisor ever become a 'secondary' supervisor? .... or can a seconadry supervisor ever become a shadow supervisor, or.. etc. OF COURSE THEY CAN. A person can and will (sometimes over time, sometimes at the same time) play many roles.
    But I disagree with this though because no manager is going to assume the position of primary AND secondary on the same project - because the nature of the job is that if primary is unavailable then secondary will take their place.

    But this is something you would not have understood as you don't have the specifics of the project like I do.

    Thanks for your input anyway, I understand what it is you are trying to say:
    Why build a system that isn't flexible to move with the business changes and development?

    And I agree

    Thanks

Posting Permissions

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