A database design for product attributes.
In this video, you'll learn how to design database tables for storing attributes for
product, such as an eCommerce store.
I've been designing databases for a long time for exercises on my website, at work, and
for side projects.
Using the fundamentals of database design, and a bit of knowledge on the subject area
can help you get the answers you need.
Let's get into the video.
So what are we designing in this video?
This was based on a question I got from a reader, "How do we design a product table,
where the product can have multiple colors and each color can have multiple sizes?"
I think there may be some many to many relationships, but it's a bit confusing.
Let's take a look at this.
I would start with a product table, this would have a definition of the product and have
things like an ID, a product name, and a description.
I would not put any colors, or sizes, or any other attributes in here yet.
We'll get to that shortly.
Next, I would create a size table.
This table will have an ID to uniquely identify the value as well as the size value.
The value of size would depend on what you're storing, but it could be numbers for clothes
or letters such as S, M, or L, or it could be either.
It would be a text value, so it could cater for different size values.
"How would you relate the product to the size table?"
Well, a product would have many sizes and a size could be related to many products.
This is a many to many relationship.
In another video I've done, I explained that the way to handle this is to add a joining
table.
So we can do that now.
We add a third title here, we can call it something generic for now, such as product
size.
In this table, we add the product ID and the size ID.
This will contain all valid combinations for the products and sizes.
This way you can have one product with many sizes.
Now we can add our color table.
Color is another attribute of a product just like size, we can treat it the same as size
by creating another table.
A color table has an ID and a color name.
We could add extra attributes here, like an image or a code for us to display on the web
page, but for now we can keep it simple.
"How do we relate to it?"
We relate the color table to our joining table.
A product can have a size, which can have a color as well.
It may seem pretty complicated, a many to many to many relationship, but the design
for it is not that complicated.
We just add the color ID to this table.
We may want to rename this joining table to something more focused like product item or
product entry to indicate it's more detailed than just a product.
We can add extra attributes as tables to the design if we need to.
For example, we could add a material or a capacity table, which we then join to our
joining table.
This joining table contains all possible combinations of product attributes.
If you've worked with eCommerce systems before, you may be aware of the concept of a SKU or
an SKU.
An SKU stands for stock keeping unit and it's a unique code assigned to each product.
This code can then be used when ordering products and uniquely identifying.
This joining table would be where the SKU goes.
You could also add in a quantity here, which would store the number of items you have in
stock.
So that's how I would recommend you design a product table with different attributes.
You can insert the appropriate values into each of the attributes and use a query to
get the values you need.
This will prove your design and produce duplication.
If you learn something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit databasestar.com, that's
where I share my best database-related content.
Which step from this tutorial was the most helpful for you?
Was it the why the attributes are stored in separate tables or that you can have a joining
table that joined several tables or something else?
Thanks for watching.