Foreign key question

MrBurns · #1 (**permalink**) 02-18-07, 12:48

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..

r937 · #2 (**permalink**) 02-18-07, 13:38

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

MrBurns · #3 (**permalink**) 02-18-07, 13:52

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..