Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    7

    Unanswered: Junction table & composite keys

    Hi,

    I'm trying to design a simple database for a project I am working on and I just need a small amount of advice to get it wrapped up. I have a junction table taking two foreign keys and they won't necessarily be unique. Is this the kind of situation where you would add an auto-incremented index? Is it possible to then compound the three fields into a compound key?

    Hopefully the picture here demonstrates my situation clearly.

    Thanks a lot for your time.

    http://dl.dropbox.com/u/1735585/database.jpg

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dontforget86 View Post
    Hopefully the picture here demonstrates my situation clearly.
    no, sorry, it doesn't

    i have a large problem understanding what "object" and "component" are

    you show a row in the junction table with values (2,1) twice

    did you intend this? because if you wanted that, you should add a "qty" column, and replace those two rows with a single one with qty=2

    ordinarily you would want (objectid,componentid) to be the PK, and you should keep looking for that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    7
    The database is for storing game object information. Object is a generic object in the game world and components are children components of the object which will have associated information like mesh location and relative position. There are more fields in the junction table because each component needs its own position specified. If any object has multiple components of the same type then the two foreign keys (which would usually make up the primary key) won't be unique. So that's the situation I'm wondering how to handle.

    So in the junction table I would have
    http://dl.dropbox.com/u/1735585/4-24...12-27%20AM.jpg

    If this still isn't understandable I'll make a complete diagram.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dontforget86 View Post
    If any object has multiple components of the same type then the two foreign keys (which would usually make up the primary key) won't be unique. So that's the situation I'm wondering how to handle.
    my advice is to use a "qty" column that says how many of this type of component are included in the object

    so the two FKs will be unique

    alternatively, since it's the x-y-z components which confer uniqueness, you should add them to the PK
    Last edited by r937; 04-23-10 at 17:39.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2010
    Posts
    7
    alternatively, since it's the x-y-z components which confer uniqueness, you should add them to the PK
    Alright, that's simple enough then. 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
  •