0
I made a diagram in the Workbench according to the instructions above, it is about the sales control in a grocery store.
My database teacher said something about not being able to visualize the price differentiation of products of the same type but different brands. For example: An instant noodle of 2 different brands, in this table, would have the same price (according to him). How can I solve this? A price only table and an N-M ratio between it and the product table?
2
Modeling is something that depends on concrete requirements. With artificial requirements anything can be right or wrong. Within my real experience making systems, your model is right because if the brand is different the product is different and should have another registration. It doesn’t make any sense to try to control objects with different specifications (no matter how minimal, it’s practically maximum) as if it were one thing.
To do otherwise would need a clarity and accuracy of requirements showing exactly what needs to be done, after all what is being asked is not intuitive and is not what you will have to do in a real scenario. That’s why I’d rather justify it instead of redoing the model. It may be that you just missed to better justify how the registration would be made and because this model is suitable, then your teacher is right, because although for sure you understood why he is right, it is the same that you do not know, and he wants you to know, not only present the result. On the other hand, your teacher may also be wanting to impose his vision and it would only make sense if he had given very clear requirements. He may have given and you may not have asked the question so we can help more.
If you don’t want to confront him, do what it looks like he hints at just to please you, even though you know it’s probably a mistake. Then I wouldn’t even know exactly what to do, a lot could be different, but since they all seem wrong, it would be a kick to offer any of them.
I think I’d have a 1:N relationship with marcas and everything but the id, nome (wow, that feels so wrong) and categoria I would move to this table. As a matter of fact, if I remove the name, I would, because the Nissin brand has a name and the Renata brand has another name, then this product table at the bottom becomes a category table and the brand table becomes again a product table. Something like:
produto
-------
id
nome
categoria
marca
-----
id
produto_id
preço
quantidade
fornecedor_id
Even in the real scenario there is a difference between manufacturer and supplier.
Anyway if I’m going to do something right I see several mistakes in this model, some because it’s an exercise and it’s not complete. So it needs to be analyzed differently. Interestingly he seems to accept these various errors of what would be a real scenario, but wants to introduce a conceptual error.
I take back everything I said if the question is not clear enough.
Thank you for your reply. Probably the teacher will correct in class tomorrow, if in case I remember I bring a more complete explanation of what he wishes.
0
Dude, I’m working on a more complex database, basically I have a modeling question, more because I apply to a standard used in the current market and I have a lot of development time and in this time my modeling is kind of personal in question to the diagram. Studying a lot I ended up stopping here, and reading your "uncertainty" I ended up stopping to analyze your modeling, by the way very well done because it is complicated for new developers understand the use of foreign key.
Let’s go to the first point, I don’t know if you still have the same doubt because it’s been 1 year, the second, with total certainty I can say that your teacher wanted to cause you a doubt in your modeling because it is very well done for a student closing college. The foreign key point "Foreign key" is the safe relation between tables, it means that when analyzing, I know that it would be impossible not to relate categories with prices, or even suppliers, because evaluating the rule of a FK (foreign key), the related id must be unique in its proprietary table, and this id you would use to relate items, I don’t know if you understood but I’ll explain it better below.
You have the products table that lists the UNICO id of the category table, the product has its price, however the category of it may be different, you could have 5 different products with the same price, but, 2 of these products do not have the same category, Therefore, when looking for products with the same price you would find the 5 products, but simply looking for products with the same price and with the same category, you would only have 3 or 2 products in your inquiry, depending on the category you specify yourself in your query. I find it unnecessary to increase the number of tables only for one price ratio, because that would be very complex from the point of view of the person who is going to consult this bank, she would have to consult another table, and when changing the price of 1 product, if this is related, you could end up changing the price of 3 products, one of which should not have its price changed.
Of two questions, one is the strongest, your teacher saw this modeling and probably knowing you didn’t think he could have this wisdom to produce such, or the other, if he imagined you could do such a thing, he might want to know how he got to this point of relationship tables, because what most would do to make this kind of relationship, would be as quoted by the last little friend up, make a table to store only prices which is a complete mistake from the point of view of people who would use their program, as 1 product may have the same price as another, however 1 product may have its price changed and the other may keep the same price, although the little friend was not wrong, it would generate a huge headache to whoever was working with this database.
Conclusion, think to me, if you have a table only for prices, and have to change the price of only 1 product, where 20 products have the same price in the table, you would have to add one more row in your price list for only 1 product, and it is easier to store the price of each product in the products table.
Congratulations for the modeling, if it was you who did, studied a lot and is on the right track, the little friend who answered first *Maniero knew how to explain in a simpler way to the general understanding, a very good answer, certainly a more experienced programmer. Big hug!
0
A good strategy is to create a price table, it can contain brand, but as this is a product attribute, the correct one should be, each product, have a brand, and the same one have a reference in the price table, with the product id as foreign key the price, and some possible attribute.
An example of a somewhat more complex database I did:
I’ll try that, thank you! :)
Browser other questions tagged database modeling
You are not signed in. Login or sign up in order to post.
Did any of the answers solve your question? Do you think you can accept one of them? Check out the [tour] how to do this, if you haven’t already. You would help the community by identifying what was the best solution for you. You can accept only one of them. But you can vote on any question or answer you find useful on the entire site (when you have enough score).
– Maniero