Link Tables

[Zahid Chaudhry]

Hi,

I want to implement a functionality in a database so that a menu can hold as many drinks and as many food items as possible. I am using a Link table for this functionality.

In Detail The DESIGN:
I have a table called venue with fields venuId, name, and menuID(foreign Key). There is a menu table with fields menuID, drinkID(foreign Key), foodID(foreign Key). There is a table called food with fields foodID, foodName, foodPrice. There is a table called drink with fields drinkID, drinkName, drinkPrice.

There is going to be a number of venues and every venue will have its own menu (i.e. In reality there are a number of restaurants with one or more menus and each menu has lots of drinks and lots of food items). If we consider a website like www.toptable.com a restaurant has the same.

Now with Design I mentioned above I can only have one drink item and one food item in each menu but I would like to have a number of drink items and a number of food items in one menu.

I hope I am not being ambiguous and unclear. Please your advise, help, suggestions will be welcome.

Thanks - Zahid