Database Design: Product Attributes

In this video, you'll learn how to design database tables to handle product attributes.

This design is a common design used in eCommerce sites or any system where you have a product that can have multiple attributes.

A simple design may have columns in a product table. But what happens if a product can have multiple attributes, each with multiple values?

[MarteeArtee]

Awesome video, its exactly what I needed right now when trying to figure out how to normalize my product variations tables!

[margaiwangara]

Thank you so much for this. I have been looking for a solution to this problem for years, I think I have finally found it.

[gsarnaudov]

Great explanation! Is it possible to make a video on product variations with different prices depending on customer groups? Actually a course for designing an e-commerce database would be of great help for lots of people. Thanks for the great content! 😎

[Shaxadd]

This is exactly what i was looking for and i literally built the worst database before this, gotta drop some tables now

[metalalive]

The data modeling looks similar to EAV pattern, except you use one table `product_entry` to tie each attribute type (e.g. size, color) with each product, and the attribute types applied to each product are presented as columns instead of rows.
May I ask question ? what if the set of attribute types applied to each product is overlapping to each other ? For example product A is T-shirt with attribute types like color, size (the value may be S, M, L, XL) ; and product B is water pipe with attribute types like color, length, internal diameter, and upper temperature limit (in this example, the common attribute applied to both is the color)

[cakecrook]

Great video! It turns out that the answer to my issue was exactly this, and I'd simply forgotten this method since I haven't worked with database design in so long!

[teamanv]

This is useful. Can you make a video of a schema where products have, for instance, over 100 attributes?

[michaelmickler]

We had to refactor ours at my last job because I don't think this scales. Most mainstream e-Commerce use more of an infinite attribute system with an attribute type and attribute value tables. The amount of tables this turns into is unmanageable.

[alexd]

no, in most situations the attributes need to be flexible. creating tables for each type of attribute is not scalable.

[anmarm.]

Thank you, i was struggling with this... it would be very helpful if you could make a small video shows sample Queries with this kind of DB.

[mohei]

Many thanks for your great idea. You are very calm teacher keep it going you're one of the best

[umaradilov]

if i would like to add any new item for attributes like (material, size) i have to add a new table ? I think this is not right

[MrPogi-lfgz]

My first thought was multiple separate many to many relations. Well done 👍.

[tanzimibthesam]

Here is sku and quantity in 3NF are both dependent on id of product_entry table

[rohitchakraborty]

Well explained! It would be great if you could provide some sample data.

[manish-ghimire]

Good video but for my purpose, it was a little incomplete. Or maybe I'm missing something :)
Can you please suggest where would the price be stated? I was looking to design a database schema where the product is priced according to the quantity bought. Eg. 1 T-shirt costs 10$ but 5 cost 7$ each.

[maxyudin]

And what about endless number of JOIN's when filtering the products by its properties? If I understand right, it is a huge load?

[kabakiAntony]

This just what I have been looking for thank you, I have one blocker though about images, will they be table on their own and then you link them to the product_entry table or will they be a field on the product entry where each variation has its own image?

[coolcha]

Wonderful video. Keep it going. I know you are just starting off but this can be a very useful channel as there are not a lot of people discussing good practices for databases. If I may a request for a future video if you can cover scenarios/use cases where using JSON data type (present in certain SQL databases e.g. MYSQL) might be meaningful?

[draybaba]

Great tutorial!!! Can you create a comprehensive tutorial on how to set up an inventory management application (POS) for a clothing store for example that will include product variations options like this? Thanks and God bless!