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

    Join Date
    Mar 2013
    Location
    Belgium
    Posts
    15
    Rep Power
    0

    MySQL Database Design problem


    I'm trying to write a Delphi Program for a member of my family who is going to open a Bakery. It's been many years since I programmed and I'm not as young as I used to be, so I'm very much a 'newbie'

    I'm trying to create a database of basic recipes, nothing too complicated - yet.

    I have the idea for the following fields in the main table.

    ID
    Itemname.
    ItemType --> link to a Table of types just an ID and a Type
    Description
    Cooking Time
    How many items in each batch
    Evaluation (short critique)
    Ingredients --> here is the problem

    Items obviously have multiple ingredients, so this needs a separate Table. I need each ingredient, the amount for a batch, the price of the item and a Table of suppliers, because they can get Items from several different places.

    There is no finite number of ingredients, it can be anything between 4 and who knows.

    Can anyone tell me how to create this kind of database and Tables. I'm just concerned about creating the database and tables at this stage. I'll worry about how to use them in the program later.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    Sounds you are on the correct path.

    As you have started, you have an recipe, with various information, basic could be
    Table:Recipe
    Recipeid
    Recipename
    CookingTime

    As you then said, you will need a table with Ingredients
    Table:Ingredients
    IngredientsID
    IngredientsName

    To match a recipe with ingredients, you will need a third table
    Table:RecipeIngredients
    Recipeid -> point to table Recipes (foreign key)
    IngredientsID -> point to table Ingredients (foreign key)
    UnitNumber -> how many units are needed
    UnitType -> what type is unit specified (kilo, liter etc.)

    Hope this will help you. Supplier can be created similar to the above, with different field names.
    Last edited by MrFujin; April 16th, 2013 at 07:28 PM. Reason: Fixed the table name for Ingredients
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Belgium
    Posts
    15
    Rep Power
    0
    Thanks for the info. I'm 100% clear about the first two tables, but I still haven't manage to completely grasp the third. Not how to create it, but actually, how ir works.

    I think if you could give me a simplistic explanation as to how it works, I might be able to figure out how to set it up on Delphi with ZeosLib.

    I think creating the three tables is easy, but understanding WHY or how it works is even more important. Could I be a real pest and ask you for a bit more detail on the workings, please?
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    The third table is needed because one recipe can use several ingredients and one ingredient can be part of one or more recipes.

    For more information, I suggest you look up "Many to many relationship" on Google search.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Belgium
    Posts
    15
    Rep Power
    0
    I didn't make myself clear. I know what you are saying, but I don't know exactly how to do it. I know what a Many to Many relationship is but I still am not clear how to implement it.

    The first two tables are almost self explanatory, it's the third table and the actual references that have me confused. My version of Table three I think may be wrong.

    I have Table RecipeIngredient : -

    ID
    Ingredient
    Price
    Quantity
    Supplier
    RecipeID
    IngredientsID

    (a) Do I need the ID at the top - I think not?
    (b) Do I set a Reference from this Table's RecipeID TO the Recipes Table or FROM the Recipes Table or doesn't it matter?
    (c) Likewise with the IngredientsID TO or FROM the Recipes Table or doesn't it matter?
    (d) What References are there from or to the Ingredients Table. Should there be a reference to to Recipes Table or the RecipeIngredients Table?

    I've never worked with tables like this. All my work before was writing Utilities for Novell eDirectory and for a simple One to Many database

    If I can get this right, I won't have to worry about doing something similar in the future. I just need to get my brain into gear on this.
  10. #6
  11. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    The idea is that you can have to recipes:
    Code:
    RecipeID	RecipeName
    1		Bread
    2		Cookies
    Both of them use different ingredients:
    Code:
    IngredientsID	IngredientsName
    1		Flour
    2		Sugar
    3		Water
    The thirds table then tell which ingredients the recipes are using
    Code:
    RecipeID	IngredientsID
    1		1		(bread use flour)
    1		3		(bread use water)
    2		1		(cookie use flour)
    2		2		(cookie use sugar)
    2		3		(cookie use water)
    and as you see in my first post, you can then extend the table with how much of each will be needed, e.g. "500 gram" flour for bread or "2 dl." water for cookies
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Belgium
    Posts
    15
    Rep Power
    0
    I understand that part perfectly. What I don't understand is exactly how to create the relationships between the three tables.

    I'm using MySQL Workbench to create the tables and relationships and as far as I can see it seems to make a difference as to which side of the relationship line the Crows Feet should be on and whether this is cascade or default or what.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by Baldur2630
    ... it seems to make a difference as to which side of the relationship line the Crows Feet should be on and whether this is cascade or default or what.
    that is absolutely correct

    i don't use the workbench but i can explain the relationships

    you have recipes, and you have ingredients, and the many-to-many relationship table will have a composite primary key that consists of the recipe_id and the ingredient_id

    the crow's feet from both the recipes and ingredients tables will be attached to the relationship table

    the cascade stuff should all be set to RESTRICT

    i can explain that last part in more detail later if you wish
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo