Database Design: Product Attributes
Vložit
- čas přidán 6. 09. 2020
- 📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🎓 Learn how to design an effective database and create it using SQL: databasestar.mykajabi.com/edd...
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? - Věda a technologie
Awesome video, its exactly what I needed right now when trying to figure out how to normalize my product variations tables!
Glad it was helpful!
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.
Great, I'm glad it helped you!
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!
Glad it helped!
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?
Good question. I think if there is more than one image per product entry you may want a separate table. I’ve seen that happen in clothing websites where you have different angles for a pair of shoes for example.
But if you don’t need that you could add a column to the existing table.
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! 😎
Good idea, I can create a course on that
Awesome video mate, I have a question for you if you could please answer me. What happen if I need to make a backoffice and allow the admin add new types of options.
I thought that one option could be create the new table dynamically. What do you think?
You could do the same thing as in this video. Or you can have a dynamic table. You can build an area of the application to allow this data to be added, in case you only want back office users to edit it and not regular users.
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)
Curious to know how this is done too! I suppose if the site had different categories e.g. women, men, home, kids etc. these would be their own individual tables, and you would have multiple 'product entry' tables. You could have a 'category' table for (tops, bottoms, shoes, bags, pipes) I suppose and join it to the individual 'product entry' tables.
I was gonna ask the same question ^^.
Actually, I am try to build a database for a wholesaler of construction material where the products in each product category has different attributes just like you mention about T-shirt and pipe. If you have already had the answer, please also let me know too.
Thank you.
Exactly! Thats what I thought too.
Good question! My video on an eCommerce Database Design might answer this question (and I think it's a better design than this video)
Hi Sir,
Can we replace with entire product catalog with Nosql DB ? ( mongodB) storing all as single document
Hi, yes you probably can. I'm not sure what the design would be as I don't have a lot of experience with NoSQL.
Would the SKU be the PK for the product_entry table? When a customer goes to purchase a product, you wouldn't store the product_id as what the customer purchased. You would need to store the SKU because that describes the product along with the accompanying attributes right?
Yeah that's right, good point. I think that's where the SKU would go.
This is useful. Can you make a video of a schema where products have, for instance, over 100 attributes?
Thanks! Great idea, I can make a video on that
Many thanks for your great idea. You are very calm teacher keep it going you're one of the best
Thanks!
Bro im trying to set this in a relational database that allows me to set a list of things to a row... as amazon db.
Should i use this structure or it changes in this case
Good question. Yes I think you could use it. I’m not familiar with that Amazon DB feature, but if it helps your design you could try it.
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?
Thanks! Glad you like it. Sure, I can create some videos on using JSON.
@@DatabaseStar I have hit the bell icon so that I am notified :).
Do you mean using JSON in a MySQL database?
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.
That's a good point. Yeah, your suggestion would work better and I've since updated my thinking on product attributes for my eCommerce design video.
This is exactly what i was looking for and i literally built the worst database before this, gotta drop some tables now
Glad you learned something from the video!
Thank you so much!
You're welcome!
hmm, this works but seems a bit tedious for my needs. Trying to design a database for jewelry products such as rings containing birthstones and it is a bit of a pain to add 12 different birthstone options and 20+ size options every time I want to make a new entry. I think I need to link another intermediary table for variations but my question is how do I create a unique SKU entry that automatically accounts for the variations this way? Any thoughts?
That's a good point. My recommendation for product attributes has changed since this video was made, and I've updated it in this video: czcams.com/video/1HamqOuv2Cw/video.html
I also have a video coming out next week that has a design and SQL focused on product attributes, which may help.
@@DatabaseStar thanks I'll check this out
My first thought was multiple separate many to many relations. Well done 👍.
Thanks!
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!
That sounds like a good idea!
@@DatabaseStar I’ll be looking forward to it. Notifications ON.
Thank you so much bro!!!
No problem!!
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.
Glad it was helpful! That’s a good idea. I can create a video about that.
Did you create this video? I was waiting to see if this video contained sample data and different ways of querying it.
That would definitely test the design to see if it really holds.
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.
Good question. The price could go into the product_entry table if the price is different for each attribute, or the product table if it's the same. If you want to have a price according to the quantity bought, I think another table would be needed: something that stores ranges of products and the prices (e.g. 1-4 is $10, 5-9 is $7, 10+ is $5).
I think you need to create a discount table and connect with product-entity
And what about endless number of JOIN's when filtering the products by its properties? If I understand right, it is a huge load?
Yeah that is a good point. Bringing data from many tables may slow it down but as long as there are indexes and the tables are small it could be OK. The query may be long depending on how many attributes exist.
I have a request to create a video and design for having 100 attributes so I’ll look into it more then.
no, in most situations the attributes need to be flexible. creating tables for each type of attribute is not scalable.
Good point. It depends on what kinds of products you have and how many different attributes. If it's only a few, this could work. If you need flexibility or if you have a lot of attributes, you may want a different design.
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
If it's a new item that uses existing attributes (such as size, colour, and material in this video), you can simply insert a new record into the product and product_entry table.
if you want to create a new attribute, such as "style", then yes you would need a new table. This would not happen very often once you've got your core set of attributes set up (depending on your business of course).
This is not for multi product variation I gues
What if we just remove the size, colour and material tables completely and replace the size_id, colour_id, material_id fields in product_entry table with size_value, colour_value and material_value respectively?? The way I see it, that would mean 3 less tables in the database, but what do you think? Is it a good alternative or should I just stick with your video?
Good question! Yes you could do that, but by adding these columns you allow for any value to be entered into the column. For example, a colour_value could be specified as Black or Red or 100 or unknown or abc123 or anything, really. Having a separate table ensures only values that exist in the related table can be selected.
You can still add values like this to the external tables, but you have greater control over ignoring them or not using them.
You could still have the values in the tables as you suggested, and it would work, there's just more of a risk.
Well explained! It would be great if you could provide some sample data.
Thanks! Yeah sample data would be useful, I can provide some for future videos.
thank you....
You're welcome!
thank you
You're welcome!
Excuse me , i have a question about relationship in this video is many to many ? or one to many
This design has one-to-many relationships.
Here is sku and quantity in 3NF are both dependent on id of product_entry table
Yeah they are both related.
Awesome sir.
Thanks!
Great video! where could I put the price, product entry?
Good question, and something I missed in this video. Yes, it would go in the product_entry table.
if i want to add the mobile Product then how would i add mobile specification of single mobile?
You could add that as an attribute if you like.
How to make this if I don't know how many attributes, I have in system ... with another word !
How to make Attributes dynamics (can customer enter any things like colours , size , material and marks )
makes variations ???
Good question! This was a common concern people had with this video. I would suggest watching my ecommerce database design video, but basically you have a table of attributes which allows for more dynamic data.
What happen if the qty and sku is in product table?
It means that you can only record the qty for a single product, and not different qty values for each size/colour/material/other attribute combination.
Product of different sizes and colours may have different prices how do you do this
I believe this would be handled in this design. Each entry in the main product table is related to different size records and colour records.
Would be awesome if you had a more realistic version. Like what if a product has a has different models and a serial number.
Thanks for the feedback! If a product has different models and serial numbers they would be different products in the tables I believe.
good one
Thanks!
Creating distribution of table such that each product with variety of variation could be stored easily.
Yes I think this table structure could do that
what if different types of products have different attributes?
This could be achieved with this design, I believe.
I don’t' know how many attributable have.i think it’s wrong way.
Have you found a better solution yet? I think it's the wrong way too.
Yeah I've since learned and read in the comments that there are better ways of doing this.
How to design this in prisma schema?
What is prisms schema?
@@DatabaseStar Prisma is a Node.js ORM and prisma schema is a file to define data model of databases. Just wanted to know how I could implement your product table design with prisma.
i didnt get how to make tables realated to each other
I’ve since created a newer version of the product database design for ecommerce that might make more sense.
Wouldn't you store a ton of redundant data this way? Like if there is a product with only one material but tons of different colors and sizes.
Yeah that's true. I've since created an eCommerce design which I think is better than this one.
@@DatabaseStar Where could I find it?
why you just dont create attribute table and attribute type table and handle any attribute dynamically!
Good question. You could, and this is called an Entity Attribute Value design. It has several drawbacks such as no referential integrity or type validation.
what if a product has multiple colors
You could change the relationship to a many-to-many by using a joining table.
@@DatabaseStar and what if each colour affects the price of the product?
@@9jawavesEntertainment In this design, I believe there's no need for a many-to-many relationship between colors and product_entries. You could just add multiple product_entries rows with different skus, colors and prices for the same product.
I think this database design is not ok. Because product_entry is a tight couple.
What do you mean by tight couple?
all three attributes have a similar field "_value", why not just create a single table called "attributes" with a column "attributte_label" or "type", and put there if its type size, color, material,to avoid the need Create tables for each attribute?
Good point, and that's a deliberate decision. The design you're suggesting is common and is called an "Entity Attribute Value". The problem is that the validation is not as good: you can't ensure that the product_entry.size_id refers to a size ID as it's just any ID in this table. If an attribute has a specific data type you can't enforce that - everything is a text value.
Your video volume is so low... Increase it
Thanks for the feedback. I agree, my videos from around this time were pretty quiet. My more recent videos have better volume and audio quality.
This structure is not dyanamic
That’s true and it’s a drawback of this design. I’ve created an e-commerce database design video which has a dynamic design for attributes
The problem with this schema is that every time you add a new product to your catalog that requires attributes other than size and color you have to make code and db schema changes. Do yourself a favor and do not follow this guy's advice on this schema, you will regret it. Your schema should allow for products to be configurable so that you do not have to make schema changes for each type of product. This is probably some of the worst advice I have seen in awhile.
Great point! Thanks for the feedback. I've since realised the same thing - if you want to have it more customisable you'll need to change the schema, as you mentioned. It's OK for a smaller set of products but not as the product range grows. I've since released an "eCommerce Database Design" video that has a better structure that is more configurable.
You could've just ended your comment on the first statement. Everything else starting with "Do yourself a favor..." is rude and unnecessary.
Every solution has tradeoffs and it's worth pointing them out, but there are civil ways to express them.
@@davosonic60 Your interpretation of "civility" , "rude" and "unnecessary" doesn't matter to me. I'm not responsible for people's feelings. I have been building software for 20 years, there has never been a real world scenario where using this particular schema would have been a consideration for production apps, that's why not using it is in fact doing yourself a favor. Besides, if you are so easily triggered then maybe the internet is not for you?
@@Broski_Rodragweez But what exactly is the suggestion ? Should all products have all attribute fields that are nullable and just fill in the appropriate ones ?
@@QwertyNPC You attributes and variants should live in linked tables. The attribute name should not be used in the table name nor any of the columns.
thats not a best solution
What solution would you suggest? There have been a few other comments saying there are other ways to do it.
@@DatabaseStar you can use one (product attributes value table) and (product attributes name table) and you will have primary key in (product table) and the foreign key in (product attributes name table) and then the primary key in (product attributes name table ) and the foreign key in product attributes value table)
Ah, so the table structure would be product - product_atttribute_name - product_attribute_value, linked by primary keys and foreign keys? That makes sense.
However, what data type do you use for the attribute values? If it's just a string, how do you ensure the data types are valid? How can you add constraints if all values are in the one field? It's a good suggestion, something I may consider for my future video on a large amount of attributes.
@@DatabaseStar to ensure database type we have ensure it on the controller side to get something we have to loose something 😂😂😂
@@DatabaseStar even I have one problem in which quantity has also need to be mapped with attributes if have 6 Quantity then need to set the quantity with attributes
This is really bad design. Not normalized at all. What if a product doesnt have an attribute ? ProductID|AttrbuteID|AttributeValue would be more dynamic and practical.
Thanks for the comment. This design is normalised as the different attributes are represented in different tables. The Product ID, Attribute ID, and Attribute Value design follows a pattern of "Entity Attribute Value", which is a common approach but also has its drawbacks. I covered that design in this video: czcams.com/video/gNlydqjmI9g/video.html
But it's probably a decision to make when you consider the whole system and what your team members (if there is a team) think.
@@DatabaseStar Thanks ! looked at other video and its amazing explanation.
Hi bro! This is really good video I was looking for this information. What if you have a multivendor ecommerce website?? So you have different products with a dynamic product_entry structure. I mean for example I sell phones and phones have brand, color, size, screen, memory, etc. And chairs with it's own attributes, and cars with it's own attributes? how should i model product_entry table?
Thanks!
Hi, you should be able to model it in the same way, as there are just more attributes to add. Alternatively you could try the design in this video on Custom Attributes: czcams.com/video/gNlydqjmI9g/video.html