Reserved user data in a database - how to structure? For example, recipes use ingredients

Question

I am creating a website for which a large amount of user data will be stored at the end of db and will be accessible to all users.

By nature, some user records will be redundant. For example, suppose these data are made from recipes. Assume the following entries:

Name: Lemon dressing:
- 1 tbsp. lemon juice
- 2 tablespoons of olive oil
Just a mix!

and

Name: Lemony marinade:
- 2 tablespoons of lemon juice
- 2 tablespoons of olive oil
- 1 tbsp. soy sauce
Just mix the ingredients together!

Obviously, these 2 entries have some redundant information: half of the name is split, instructions are split partially, one ingredient is split exactly, the other component is partially split ....

In addition, it was easy to imagine a recipe of several sub-recipes, which could also exist on their own. So, for example, you might have a pickled steak recipe that uses lemon marinade as a step. Thus, recipes can be defined recursively.

My question is, how do I create an SQL recipe database? I feel that it makes sense to keep the record "lemon juice" in the table "Ingredients", the amount of "2 tbsp." in the amount of 1, and then use their keys in the recipe table.

? " " , " " " zest"? , .

, , , , , , . ? ?

, . , , - . , , ?

,

JDelage

Answer 1

-, . , , . , . , . , , , , .

, , ProductBase

, , .. . , , , , , , , . , , , ..

, A, A1, A2 .., A1, A2 (.. , -) - . .

, , .., , ParentProductID,

- .

, , INGREDIENT, LinkedProductID .

CREATE TABLE `productbase` (
    `productid` CHAR(30) NULL,
    `productname` VARCHAR(200) NULL,
    `parentproductid` CHAR(30) NULL,
    `linkedproductid` CHAR(30) NULL,
    `categoryid` CHAR(30) NULL,
    `supplierid` CHAR(30) NULL,
    `type` CHAR(30) NULL,
    `subtype` INT(11) NULL DEFAULT NULL,
    `cost` DECIMAL(10,4) NULL DEFAULT NULL,
    `mcu` CHAR(30) NULL,
    `mcuperpack` DECIMAL(10,4) NULL DEFAULT NULL,
    `quantity` DECIMAL(10,4) NULL DEFAULT NULL,

)

Answer 2

, : , , , , . ; , , ORM/.