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!
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