Results 1 to 12 of 12

Thread: Table Question

  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Table Question

    I have the table Items which is a child of Platform. Each platform can have multiple items. I need to make a new table, which I will call WPE which can also have multiple items. What would be the best way to make this work. Should I make a new foreign key in Items?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by evant8950
    Should I make a new foreign key in Items?
    if you want Items to be a child of WPE, then the answer is yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2009
    Posts
    7
    Thanks. So it is ok for items to be a child of platform and WPE? I was wondering if this is a situation where you needed to use a junction table.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by evant8950
    Thanks. So it is ok for items to be a child of platform and WPE?
    sure, in exactly the same way that it's okay for a product to be a child of a vendor at the same time as being a child of a product category

    Quote Originally Posted by evant8950
    I was wondering if this is a situation where you needed to use a junction table.
    probably not

    but note that if both of the FKs are declared as NOT NULL, then the items table is a junction table between platforms and WPEs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2009
    Posts
    7
    Some items will not be a child of WPE, so it can be null. So items will not be a junction table. My items table looks like it would be a junction table.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I would do it like this and without nulls:

    WPE {WPEId} KEY {WPEId}
    Item {ItemId, PlatformId} KEY {ItemId} (or {ItemId, PlatformId}??)
    ItemWPE {ItemId, WPEId} KEY {ItemId} (or {ItemId, WPEId}??)

    You don't need nulls to represent any kind of relationship.

  7. #7
    Join Date
    Aug 2009
    Posts
    7
    It would look something like this:

    Platform - WPE
    | |
    Item-------

    But each item may not be a child of WPE at all.
    Last edited by evant8950; 08-04-09 at 15:38.

  8. #8
    Join Date
    Aug 2009
    Posts
    7
    That didn't come out correctly the second pipe is suppose to come down from WPE

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I think my suggestion is pretty close. You just need to decide whether the relationships are 1-many or many-many and choose your keys accordingly.

  10. #10
    Join Date
    Aug 2009
    Posts
    7
    If that particular item has no WPE then that foreign key is null. Correct?

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by evant8950
    If that particular item has no WPE then that foreign key is null. Correct?
    If an item has no WPE then it won't have a row in the ItemWPE table at all. No need for a row with a null in it.

  12. #12
    Join Date
    Aug 2009
    Posts
    7
    I see what you are doing. I thought of something similar. I just needed some direction on which route to take. 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
  •