Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Posts
    3

    Foreign key question

    Hello,

    I'm developing a web app for an online food ordering system:
    There will be multiple food outlets selling on the system.

    Upon attempting to normalize my DB I came up with the following issue in 2NF:

    Food menu Item entity:

    Food outlet ID (FK)
    Menu ID (FK)
    Item name

    Description
    Category (FK)
    Price

    Then I have another entity Describing a complementary item for each food menu item as follows:

    Complementary item entity:

    Food outlet ID (FK)
    Menu ID (FK)
    Menu item name

    Menu item complementary item (FK)

    Now, the issue is:

    How do I uniquely identify and associate a menu item with a complementary menu item? considering that a menu item is uniquely identified by:

    Food outlet ID
    Menu ID
    Item name

    Also, note that a complement will only be an item on the same menu (there may be multiple menus in the system for a particular outlet).

    Thanks for any help you can provide..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is homework, right?

    clearly, you need a relationship between the two entities

    can a single food menu item have multiple complementary items?

    can a single item be complementary to more than one item?

    yes to both, right? so it's a many-to-many relationship
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2007
    Posts
    3
    Not homework.

    A menu item will have only one complement. A menu item can be a complement to multiple menu items.

    The issue I'm having here is with the compoiste key. A menu item is identified by Food outlet ID, Menu ID, Menu item name.

    To associate a menu item with a complement, I think the following might suffice, but I not sure:

    Complement entity:

    Food outlet ID(FK)
    Menu ID (FK)
    Menu item ID (FK)

    Menu item complement ID (FK)

    (each underlined attribute is a partial primary key).

    The other issue here is, if the above is correct, the there is only one attribute in the difference between the complement entity and the menu item entity. Would it make sense and be correct in terms of normalization, if I were to place the complement attribute into the Menu item entity?

    Thanks again..
    Last edited by MrBurns; 02-18-07 at 15:02.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MrBurns
    Would it make sense and be correct in terms of normalization, if I were to place the complement attribute into the Menu item entity?
    most definitely, yes

    Food menu Item entity:
    Food outlet ID (FK)
    Menu ID (FK)
    Item name
    Description
    Category (FK)
    Price
    Complementary item (FK)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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