Relational database model with SKU

Question

I am creating a system for selling products and I need that these products can have different prices according to their size or color, for this I am using SKU, I am creating the relational model of this database and would like to know if this model I created is suitable and will allow me to browse the products according to the SKU and it brings me all the information like product name, price, etc.

For example:

If I have a size P and G boot, and in Color Pink and Black, I would have to be able to search for a Black Boot of size P, or a G of color Pink, using for this the SKU, with this model I created this would be possible?

Follows the model:

If anyone wants import the template into your Mysql Workbench.

Answer 1

The model you have made should meet the current needs of your project, should not worry so much about what may occur in the future, the most important is that it meets the needs of now and that is with a well-connected structure. The more abstract, the simpler, the better. However, this should not be confused with a lack of parameters or tables. One way that you can do to be able to build a database in a nice way: think as if you were going to build a company with offices, departments, where you should create each sector, and each with its proper responsibility, Now think about how they should communicate with each other. Starting from this abstraction, it is easier to understand how entities and their relationships will work. What you see in the future, you implement later.

Obs: Just remembering that what you’re doing is basically rewriting the wheel, since there are ready-made models of cms that have well-designed structures for product databases. Like the virtual stores: Oscommerce, Woocommerce, Prestashop, Magento etc..

There is even other models that you can base, to study yours that are already ready. Just do a little Internet search by MER, DER, ER. Here you go other examples.

Answer 2

It seems appropriate, but it may not be. You are the person who knows the real problem the most and has doubts, imagine we who have no idea where it will be used, how, what the needs of today and tomorrow.

What I mean is that so much can go wrong or change, so random people on the Internet are less able to tell if everything will work out than you are. You have to do the best you can and be prepared for possible changes, do nothing that makes it difficult to change later. That’s very rare to see, therein lies the real problem.

I actually have a lot of doubts if you need all this. It seems to me that in fact some of these tables have relation 1:1 and in this case I do not see much advantage to be in separate table. For me the tables linked to the SKU should be simple columns in the table itself, even after the comment below I see no reason for these tables to exist. I would probably simplify by deleting 5 tables. But who am I to tell you what to do, there may still be something I don’t know, so it’s hard to answer.

I have even doubts whether there should be a separation between product and SKU. I even see some usefulness, but it may not be necessary. Many systems have what would be only the SKU, after all if each SKU is different, is another product and what calls product maybe is a group.

I find it strange produto_tamanho have relationship with the product and with SKU, this seems an error.

I won’t even mention the confused names, that’s taste. Not everything should be VARCHAR(45), this is a problem that goes beyond modeling, that is, this is a problem so big that doing so any modeling will "serve". This kind of thing will hinder future changes. But I imagine that what will make it more difficult to even see in this model.

If the question had posed as text and not as image I would have reformulated the template and posted here as I would.