Doubts in the modeling of the Database

Asked

Viewed 245 times

3

Guys, I have the Product, Ingredient and Sale table. The product contains several ingredients, e.g.: Suppose that the product is a mixture, it contains 1 bread and 2 Ingredient cheeses, this composition is stored in a table N for Product and Ingredient N. But in the meantime, however, the customer can add more ingredients, so I thought I would create a table with these ingredients used in selling that product. However, I have no idea what this modeling would look like. Can anyone help me?

inserir a descrição da imagem aqui

Product Ingredients is the relation that tells which ingredients have in the product and I’m in doubt in Ingredientes_used, pq how do I connect product, ingredient and sale so I can list the used ingredients of each product? Because the user can add in his snack, bacon, salad, etc that is out of pattern that is stored in the product_ingredients.

mysql sql database oracle modeling

  • But do you want to register these additional ingredients in the database for each order you place? Maybe another specific relationship for these additional ingredients can meet.

  • Yes, the idea is to list these additional ingredients used in the sale, but I have no idea how to start :c. What happens, these additional ingredients are already registered in the bank in the ingrediente_product table, there he can add bacon, etc. Except that in the case to list these "new" ingredients used, there should be another table and this table is confusing for me, because I don’t know if I can call Sale, Product, Ingredient, or if it has a better shape.

  • It would be another table, similar to the ingredient-product, specific to the optional items actually added and also linked to sale. It seems to me that it should not be related to product.

  • But how would I know that the ingredient used was used in a particular product? Like, I bought All-you-Can and X-Salad and added an egg to the X-Salad, like I’m gonna know that that egg is from the X-Salad?

  • 1

    The egg is not related to all salad x, it is related only to that specific salad x whose client requested. It could be added to x-salad or any other product. You have to precisely define the requirements of your model and what it needs to meet.

  • That’s what I want you to mention. In this case I would link ingredients - used ingredients - sales?

  • Table venda_produtos listing sales and products. Table venda_produto_adicionais relating the product of that sale to the additional ingredients.

  • That table, sales_additional product_would have sale, product and ingredient?

  • You already solved your problem. What is missing is to create a view of 'products' x 'igredientes' with 'product_igredientes' as pivot plus the insertion, deletion, update procedures.

  • 1

    @Augustovasques I didn’t understand why I had to create a view and procedures. Only with business tables and operations doesn’t meet the requirements of the AP? I cannot see usefulness in the view because it is a concept with which I had little contact, suddenly you have another vision (with the pardon of the pun).

  • 1

    @Gabrielsereno Just a few perfumes, his modeling is missing a field amount in the product table (e.g.: mixed = 2 cheeses) and in the other tables change the field type value of float for decimal.

  • @Gabrielsereno, is that you did it right. Modeling meets your problem, as Piovezan said only missing details. I suggested a view because the view enables an integrated view of your data, but its use is not mandatory. It just makes it easier to search for data to display.On the front end instead of using joins in each search you would use a simple select. But that’s just detail.

  • Thank you guys :D

  • @Piovezan. Excuse me, just now I identified that the directed comment was made by you. I answered it directed to Gabriel. Piovezan table and business operations yes meet the requirements. The view is a facility that aims to concentrate data from multiple tables into a single virtual table. What is the advantage? The advantage is simplification of reasoning, rather than looking at a set of tables, analyzing their relationship structures to then deduce a data structure the use of the view shortens these inferences by directly exposing the intended data structure.

  • 1

    @Augustovasques Got it, thanks for the clarification.

Show 10 more comments

2 answers

1

Dude you can put a "type" column (or any name you want) in the product table_ingredients and put values like "standard" or "added", or even 0 and 1, to tell if the ingredient is part of the original product or has been added.

Then with an Inner Join you can list the added ingredients in each snack.

1

Dude, the most practical method I learned (and entered my head) was: to make the table by hand (or in excel) of the final result of the data you expect to obtain and/or save (eg: command, invoice record sheet of requests). And from there, it normalizes, at least, until the third normal form.

At the end, you will be with all relationships ready without having to break your head with business rule.

Summarizing: Draw your initial form > Normalize fields

About normalization, has enough material on the internet for all tastes, from video classes on youtube to academic works.

Browser other questions tagged mysql sql database oracle modeling

You are not signed in. Login or sign up in order to post.