Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: 2 tables interacting with each other

    Hi guys,
    i've been faced with this dilema about basically designing a sql database.

    my requirement is for two tables.
    first table product.
    second table ingredient.

    I have a set of ingredients = IDs and text

    Similarly with with product = ID and text

    heres the problem, product1 = (ingredient1 + 2 + 4 etc..);
    each product in the products table is made up of a set of ingredients.

    What i want is, when I select any set of ingredients, it should yeild all the products the selected ingredients are in.

    how do i go about it?
    i designed a basic model
    ingredients table and products table are connected via a mapping table.
    the mapping will hold all IDs.
    product 1 = ingr 1
    product 1 = ingr 2
    product 1 = ingr 4

    thats how it is. But i wana know if there is anny better way of going about this. specially if
    1= i want to add another table like ingredients and connect it to products
    2= what if I want two similar ingredients two hold one ID? (its not possible .. but.. )

    any help / alternatives is appreciated.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by chronox
    my requirement is for two tables.
    first table product.
    second table ingredient
    I think the correct technical term you're looking for is coursework

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chronox
    1= i want to add another table like ingredients and connect it to products
    why would you do that? the three tables you already have (products, ingredients, and the mapping table) are sufficient

    Quote Originally Posted by chronox
    2= what if I want two similar ingredients two hold one ID?
    why would you do that? there is no need
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2009
    Posts
    2
    thanks r937!,

    So you think my approach is right? and theres not a better way?

    I wouldnt do those, its true, but I was thinking of a future scenario "what if i add.."!

    and this isnt a coursework. I wish you'd atleast mention Google..!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chronox
    I wish you'd atleast mention Google..!
    i'd be happy to: Let me google that for you
    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
  •