#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Belgium
    Posts
    15
    Rep Power
    0

    One to many Relationships - HELP!


    Let me prefix this by saying that I'm retired, 72 years old, ex-Computer Engineer, programmed in VB and Delphi, but NEVER worked with one to many relationships, because I never needed to for the work I did.

    Now I'm trying to get my head around One to many Relationships, but going quietly crazy. I'm trying to create these Tables at Runtime in Delphi. I've done this lots of times, but never had to set up relationships, but if I have the SQL or the Table Structure, I can program it.

    I've tried using MySQL Workbench. A great Tool IF you know what you are trying to do (and I don't!)

    Here are my Tables - it's pretty obvious what I'm trying to do, can someone PLEASE give me the SQL to create this along with the relationships? save me from bashing my head against a brick wall. (The Workbench complete diagram would also do. I think)

    TABLE Recipe
    RecipeID INTEGER,
    ItemName VARCHAR(40),
    Type INTEGER,
    Description VARCHAR(200),
    PicLocation VARCHAR(40),
    BatchSize VARCHAR(50),
    Time2Cook VARCHAR(50),
    Comments VARCHAR(200),

    TABLE Ingredients
    IngredientID INTEGER,
    Name VARCHAR(60),
    Supplier VARCHAR(60),

    TABLE RecipeIngredients
    IngredientID INTEGER,
    Amount VARCHAR(50),

    TABLE Types
    TypeID INTEGER,
    Name VARCHAR(60),
    PRIMARY KEY iType (TypeID),
    UNIQUE INDEX iName (Name)

    Thanks in advance
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,295
    Rep Power
    9400
    So this RecipeIngredients table is to list the ingredients that a recipe uses, right? That means it needs to mention both the recipe and ingredient. You've got something for the ingredient in there now but nothing for the recipe, so add another column for the recipe ID.

    And actually what you're doing is a many-many relationship. Maybe that helps with some of the confusion? A particular recipe can have many ingredients, and a particular ingredient can be used in many recipes. Many, many.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Baldur2630
    ... can someone PLEASE give me the SQL to create this along with the relationships?
    somebody already did

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo