April 16th, 2013, 08:40 AM
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.
ItemType --> link to a Table of types just an ID and a Type
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.
April 16th, 2013, 02:54 PM
Sounds you are on the correct path.
As you have started, you have an recipe, with various information, basic could be
As you then said, you will need a table with Ingredients
To match a recipe with ingredients, you will need a third table
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 08:28 PM.
Reason: Fixed the table name for Ingredients
April 16th, 2013, 05:33 PM
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?
April 16th, 2013, 08:30 PM
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.
April 17th, 2013, 03:55 AM
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 : -
(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.
April 17th, 2013, 04:58 AM
The idea is that you can have to recipes:
Both of them use different ingredients:
The thirds table then tell which ingredients the recipes are using
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
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)
April 18th, 2013, 10:57 AM
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.
April 18th, 2013, 11:42 AM
that is absolutely correct
Originally Posted by Baldur2630
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