Results 1 to 9 of 9

Thread: Link Tables

  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Question Link Tables

    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.

    Please have a look at the link Tables.doc file attached. There is a picture (er diagram) of part of the database.

    Your help might help...

    Thanks - Zahid
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how many menus are you planning to have?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Posts
    5
    I am planning to have one menu. To be a bit more specific. There would be one venue table which would have the menu table as a foreign key. Hope that explains it.

    Thanks - Zahid

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have a wonderful idea -- if there's only going to be one menu/venue, you don't need a table for it

    instead, just assume that every food and drink is on it

    do not enter any foods or drinks into the food or drink tables that aren't on the menu/venue

    simple, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2007
    Posts
    5
    Hi,

    Ummm...
    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 menu or more and one menu has lots of drinks and lots of food items). If we consider a website like www.toptable.com a restaurant has one menu and and lots of drinks and food items.

    Coming to what you said it means that I need to have a fixed number of food items and drink items in the menu. I don't want to restrict the design by adding a fixed number of keys.

    I hope I am not being ambiguous and unclear.

    Thanks - Zahid

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, not ambiguous or unclear, although this now contradicts what you said in post #3

    okay, let's have a Menus table, a Foods table, and a Drinks table

    now add two more tables, each of which will be a many-to-many relationship table -- MenuFoods and MenuDrinks

    each of these tables will consist of only two columns: the foreign keys to the related tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2007
    Posts
    5
    Hi,

    Thanks a lot for that.
    I nearly understand now how to do it but have a problem still. Please have a look at the attachment.

    Your help has been grate.

    Thanks - Zahid
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Venues
    1001 Todd's Restaurant
    1002 Biff's Steakhouse

    Menus
    21 1001 Lunch at Todd's
    22 1001 Dinner at Todd's
    23 1002 Biff's Allday Menu

    Foods
    54 New York Steak
    56 Cabbage
    57 Potatoes
    58 Ice Cream

    Drinks
    78 Beer
    79 Whiskey
    83 Sambuca

    MenuFoods
    21 56
    21 57
    22 54
    22 57

    MenuDrinks
    21 78
    21 79
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2007
    Posts
    5

    Smile

    Thanks a lot.

    You have been of grate help, I now understand. In the future I might have more questions but for now you have cleared this one .

    Thanks - Zahid

Posting Permissions

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