Want to improve your database design skills? Get my Database Design project Guides here (diagrams, explanations, and SQL scripts): databasestar.mykajabi.com/lpkj-dbdesign/?
wow. i just started learning sql. i watched some videos for mysql, mariadb, postgresql etc. with the basics how to create and fill tables with content. but in none of the videos there was any similar way of working with workbench like you did. this was VERY helpful for me and should be a key video for everyone learning sql and db design.
Thank you so much for the attention to detail in making these practical videos. They were really great resources for me to learn/review key concepts during my job search, which I was fortunate enough to land! Is there a way for your viewers to support your work monetarily so you can continue to make these videos?
Thanks Kevin, glad you like it! Glad to hear they have helped you get a job! Yes, I do offer paid courses on SQL and database design, which are available here: www.databasestar.com/course-list/
Thanks for this great content! I was getting really confused with so many product attributes but you make it seem easy. I'll practice a little to fully understand and hopefully convert the database design to hibernate
How do we handle multiple kinds of product variation.17:29 Example: In the above example the product_variation table captures the variation in size , but what if we also had to capture the variation in colour as well i.e. suppose product_code should have remained same for all the size and colour combination. Should we have then moved the colour_id too to the product variation table? Then I think we would have qty for the combination of size and colour.. Also in that case I think the product variation table would not have been necessary. Thanks
Good question, yes if that was the scenario then I believe the product variation would not be needed, and capturing the qty for the combination of size and colour could work.
I think it better for primary key to have Serial datatype(auto increasing number), unless there is a custom format being followed, like car license plate. Thanks
I have a quick question so don't you think that there should be a Many to Many relation between ProductCategory and Attribute Type to ease the filter that the user going to make according to selected ProductCategory? I think it would be much easier for me to show Products to the users who has selected ProductCategory has these Attirbutes that can be filtered on and I can show them realted Products based on filter that they made?
@DatabaseStar For anyone else who is reading this: I use mono-spaced fonts anytime I am indicating, on a Web page, in print, etc., something to be typed. Can be code, file names, etc., except web and email addresses. This makes it easier to see exactly what is to be typed as the spaces are clearly visible and the I (uppercase i), l (lowercase L), and 1 (one) are, usually, better differentiated. Not always so with O (letter O) and 0 (zero), but more so than with proportional fonts. I have seen editors that would restrict the font choices to mono-spaced fonts. Why this is not so for _all_ code editors is beyond me.
Thanks for the video, very informative! In the context of this diagram: - how can I differentiate which product colors are available and which are sold out and display this - the same with dimensions. What sizes are currently available and which ones are not?
You can relate the product_variation table (which has the qty_in_stock column) to the product_item and then the colour_table (which has the references of colours) to see which items are in stock.
@@DatabaseStar won't the new relation be redundant? SELECT s.size_name, pv.amount > 0 as in_stock FROM product_variation pv INNER JOIN product_item pi ON pi.product_item_id = pv.product_item_id INNER JOIN size_option s ON pv.size_id = s.size_id WHERE pv.product_item_id = 1; I only have this idea for now
Thank you for this video. I am looking for a website template like the one you are "ASOS" that you are using. Where can I clone your project? I need to use this for a hardware eCommerce site.
Awesome - but quick question if you don't mind - at what point does an attribute become a variation property, or vice versa? IE what if the website started selling hair clips that don't have a size?
Ahh, it's to allow the stock level to vary based on the variation. So if I wanted the price to change based on a variation, say color, I'd have to put both the price and color entries in the product_variation table?
Thanks alot for this. I am working on mine and this is just what I needed to get it going in the right direction. A couple of questions: what are your thoughts about using visio as a diagram design layout mechanism.? question number 2: I am considering setting up a test web server on a separate metal box different from my sql box. Is there a way to have my webserver to connect to the sql server for the data storage etc or should i put them on one machine?
Thanks, I'm glad you like it! 1 - Visio is a good tool for database design. It's been around a while too. I often don't use it as the places I work don't have a license for it (and I don't use it at home), but I have used it in hte past. 2 - I think this would be possible, but the details are outside of my area of expertise. I assume you would have to allow connections on certain ports.
very understandable 💯 but i think using that EAV approach it will not fit when your products are linked variations like "BLACK color SM size SILK fabric..." like that first approach would work, could it cause u can retrieve one buy one no way to linked them to be select as one choice while being a combination of different features
I think I understand your concern, but I think the query would be OK. You can get all of the product variations in one query and their variations, and display them on the page as needed.
Thanks, I am building the Product Data Model in Data Vault 2.0, and struggling to get the concept to be implemented in DV format. Any pointers help will be really helpful.
No problem! I don't actually have any experience with Data Vault so I can't be of much help, but I hope the concepts of normalisation would still apply.
@@DatabaseStar like PayPal or Wise! Demonstrating how can you design a database that requires transactions using money on balance, credit card and stuff like that
Hello, i am trying to create an online shop that might sell not only clothes but other products as well like laptops. How would i create the database so it can accommodate clothes and laptops for example which can have different attributes (size for clothes and processor_type for laptops). Thanks
excuse me, i am newbie, i want to ask the code from the video is can use on dbvear or MySQL workbench? i still difficult to used them. i am happy if you englightmen
Brother the script U provided in Ur GitHub repository has possible errors, I ran it in MySQL workbench but it failed saying "Error Code: 1824. Failed to open the referenced table 'attribute_type' ". Can U plx fix this ASAP as I have to practice queries on some meaningful data. Plx provide the correct script to create this database or guide me if I made a mistake.
Sure, what do you mean "for w3school"? Like, view their website and create a database design for it? If so, w3school seems to be a simple blog, is that what you're looking for?
Why creating product_gender when its just a parent category for subcategories and can be named just as product_category with attributes of id, name and parent_id so this way you have one table handling the logic for infinite level of categories.
Want to improve your database design skills? Get my Database Design project Guides here (diagrams, explanations, and SQL scripts): databasestar.mykajabi.com/lpkj-dbdesign/?
This is one of the videos to Design a Database. Appreciate your time to make this video.
Glad it was helpful!
wow. i just started learning sql. i watched some videos for mysql, mariadb, postgresql etc. with the basics how to create and fill tables with content. but in none of the videos there was any similar way of working with workbench like you did. this was VERY helpful for me and should be a key video for everyone learning sql and db design.
Wow thanks a lot! Glad you found them helpful.
Wow same here let's connect
Wow same here let's connect
this is the best video I have seen before. thanks a lot !!!!
Wow thanks! I’m glad you like it.
Thanks for your video I have a RDBMS final test at this week and tbh, i was so confused abt everything i've learned in my class. You help me a lots.
You're welcome, glad it helped!
Thank you so much. I am building the erd diagram for my own business and this helped me a lot. I appreciate your videos bro
Glad it was helpful!
Great to see how it actually works in a real world
Glad you found this format useful!
This amazing! Please keep making more!
Thanks, I will make more like this!
Thank you so much for the attention to detail in making these practical videos. They were really great resources for me to learn/review key concepts during my job search, which I was fortunate enough to land!
Is there a way for your viewers to support your work monetarily so you can continue to make these videos?
Thanks Kevin, glad you like it! Glad to hear they have helped you get a job!
Yes, I do offer paid courses on SQL and database design, which are available here: www.databasestar.com/course-list/
This is the best video I have seen before!
Thanks! I’m glad you liked it.
Oh my God , this tutorial is GODLIKE. Your the best sir. Thank thank a milliooooonn thank you very muchhhhhhh.
Wow thanks a lot, I’m glad you liked the tutorial!
Thanks for this great content! I was getting really confused with so many product attributes but you make it seem easy. I'll practice a little to fully understand and hopefully convert the database design to hibernate
Thanks, I'm glad it helped!
How do we handle multiple kinds of product variation.17:29 Example: In the above example the product_variation table captures the variation in size , but what if we also had to capture the variation in colour as well i.e. suppose product_code should have remained same for all the size and colour combination. Should we have then moved the colour_id too to the product variation table? Then I think we would have qty for the combination of size and colour.. Also in that case I think the product variation table would not have been necessary. Thanks
Good question, yes if that was the scenario then I believe the product variation would not be needed, and capturing the qty for the combination of size and colour could work.
this is top content!! thanks a lot!!
Glad you like it!
Excelent video, regards from Caracas, Venezuela
Thanks a lot!
nice in depth explination. thanks for your efford :)
Glad it was helpful!
I think it better for primary key to have Serial datatype(auto increasing number), unless there is a custom format being followed, like car license plate.
Thanks
Good idea, I usually use auto-incrementing integers (such as Serial in Postgres).
Thank you so much. You explained it very nicely.
Glad it helped.
appreciate this comprehensive video!
Glad it was helpful!
Thank you, very useful to me.
Glad it was helpful!
This is so helpful! Thank you!
Glad it was helpful!
I have a quick question so don't you think that there should be a Many to Many relation between ProductCategory and Attribute Type to ease the filter that the user going to make according to selected ProductCategory? I think it would be much easier for me to show Products to the users who has selected ProductCategory has these Attirbutes that can be filtered on and I can show them realted Products based on filter that they made?
Yeah that's a good idea, it could work.
@DatabaseStar Thank you for your feedback because it can be hard to validate myself .
So much high quality information
Thanks! Glad you liked it.
This is so amazing
Thanks! I’m glad you like it.
Do yourself, and everyone else, a favor. Set your code editor to use a mono-spaced font!
Great idea! My other editors are set this way, but I've just used the default for MySQL Workbench which is not monospaced. I'll change it. Thanks!
@DatabaseStar For anyone else who is reading this: I use mono-spaced fonts anytime I am indicating, on a Web page, in print, etc., something to be typed. Can be code, file names, etc., except web and email addresses. This makes it easier to see exactly what is to be typed as the spaces are clearly visible and the I (uppercase i), l (lowercase L), and 1 (one) are, usually, better differentiated. Not always so with O (letter O) and 0 (zero), but more so than with proportional fonts. I have seen editors that would restrict the font choices to mono-spaced fonts. Why this is not so for _all_ code editors is beyond me.
I want you say Thank you. 🙏🙏🙏
You're welcome!
Thanks for the video, very informative!
In the context of this diagram:
- how can I differentiate which product colors are available and which are sold out and display this
- the same with dimensions. What sizes are currently available and which ones are not?
You can relate the product_variation table (which has the qty_in_stock column) to the product_item and then the colour_table (which has the references of colours) to see which items are in stock.
@@DatabaseStar won't the new relation be redundant?
SELECT
s.size_name,
pv.amount > 0 as in_stock
FROM product_variation pv
INNER JOIN product_item pi ON pi.product_item_id = pv.product_item_id
INNER JOIN size_option s ON pv.size_id = s.size_id
WHERE pv.product_item_id = 1;
I only have this idea for now
Thank you for this video. I am looking for a website template like the one you are "ASOS" that you are using. Where can I clone your project? I need to use this for a hardware eCommerce site.
I don’t know where you would get a template from. I just followed along with this website and created the database.
Thank you very much sir, this video is really helpful. Btw, is this technique called "database custom filed with EAV model"?
You’re welcome! Yes this is a kind of EAV model, the variation table and related tables are similar to that.
Thanks for the great video. Hopefully , you will create a eCommerce database design that has multi vendors like amazon or alibaba
You're welcome! Good idea
Awesome - but quick question if you don't mind - at what point does an attribute become a variation property, or vice versa? IE what if the website started selling hair clips that don't have a size?
Ahh, it's to allow the stock level to vary based on the variation. So if I wanted the price to change based on a variation, say color, I'd have to put both the price and color entries in the product_variation table?
ha, that still makes price variations based on *size* difficult though... interesting... :D
Yeah that's right, it depends on how you want to track stock or prices. This is just a suggested design though.
@@DatabaseStar yeah for sure, enjoying it, and your emails. thanks :)
Thank you so much
You're most welcome
Thanks alot for this. I am working on mine and this is just what I needed to get it going in the right direction. A couple of questions: what are your thoughts about using visio as a diagram design layout mechanism.? question number 2: I am considering setting up a test web server on a separate metal box different from my sql box. Is there a way to have my webserver to connect to the sql server for the data storage etc or should i put them on one machine?
Thanks, I'm glad you like it!
1 - Visio is a good tool for database design. It's been around a while too. I often don't use it as the places I work don't have a license for it (and I don't use it at home), but I have used it in hte past.
2 - I think this would be possible, but the details are outside of my area of expertise. I assume you would have to allow connections on certain ports.
very understandable 💯
but i think using that EAV approach it will not fit when your products are linked variations like "BLACK color SM size SILK fabric..." like that first approach would work, could it cause u can retrieve one buy one no way to linked them to be select as one choice while being a combination of different features
I think I understand your concern, but I think the query would be OK. You can get all of the product variations in one query and their variations, and display them on the page as needed.
Thanks, I am building the Product Data Model in Data Vault 2.0, and struggling to get the concept to be implemented in DV format. Any pointers help will be really helpful.
No problem! I don't actually have any experience with Data Vault so I can't be of much help, but I hope the concepts of normalisation would still apply.
@@DatabaseStar No worries, thanks for the response. Will keep trying.
Congrats, great video! I would love if you teach us how to design a digital wallet database!
Thanks! Sure, what do you mean by a digital wallet database?
@@DatabaseStar like PayPal or Wise! Demonstrating how can you design a database that requires transactions using money on balance, credit card and stuff like that
Hello, i am trying to create an online shop that might sell not only clothes but other products as well like laptops. How would i create the database so it can accommodate clothes and laptops for example which can have different attributes (size for clothes and processor_type for laptops). Thanks
You can use the database design in this video, I think, and the product categories would be where you would have clothes and laptops.
excuse me, i am newbie, i want to ask the code from the video is can use on dbvear or MySQL workbench? i still difficult to used them. i am happy if you englightmen
You can use it in any editor in SQL. You may need to make some minor adjustments for each database, such as data types, but it should work.
Sir it is possible to include product category as an attribute of product ?
Yes, you can join to the category table for this.
Thank you
You’re welcome!
Brother the script U provided in Ur GitHub repository has possible errors, I ran it in MySQL workbench but it failed saying "Error Code: 1824. Failed to open the referenced table 'attribute_type' ". Can U plx fix this ASAP as I have to practice queries on some meaningful data. Plx provide the correct script to create this database or guide me if I made a mistake.
Hi Xubi, as mentioned over email, the script has not been updated. The issue was caused by the tables not being created in the correct order.
Can you make a similar video for a movie ticket booking application
Good idea, I can do that
@@DatabaseStar thank you, looking forward to it
I love you. you are the best.
Hahah thanks!
how i insert image for productItem, example, has 5 size and 5 color -> has 25 product item???
You can insert separate rows for each option.
Thanks a lot! Can I have diagram and script. Thanks
Sure! You can get them at the link here, which is also in the description: www.databasestar.com/dbdesign/
how if some product dont have size ?
is the size_id on product will be null ?
It could be null, or you could have a Size value of "not applicable" and relate that.
@@DatabaseStar okay thanks
thanks bro
No problem!
Can you please make a practical video like this for w3school
Sure, what do you mean "for w3school"? Like, view their website and create a database design for it? If so, w3school seems to be a simple blog, is that what you're looking for?
@@DatabaseStar yes and we add more feature
what name software for create database design like this video?
It’s called LucidChart.
can we use jsonb fields as alternative EAV
Yes you can, it has a similar kind of flexibility.
I am currently implementing a jsonb field for a project im working on. Remember, querying such a field comes with its own downsides
How to Design a Database for cityguide products
What’s a city guide product? I have a video on my channel about designing a database so you can follow that process.
Please design how to design permission based system there are mutiple actors .each actor has some permission
Good idea, I can do that.
Where can I find the SQL code for this database?
You can find it in the link in the description, which is here: www.databasestar.com/dbdesign/
Why creating product_gender when its just a parent category for subcategories and can be named just as product_category with attributes of id, name and parent_id so this way you have one table handling the logic for infinite level of categories.
That’s a good point, it could just be another category. I may have mentioned that in the video, but if not, then yes it could work.
What use application for database design
It’s called Lucidchart
Where is the direct link to Schema?
What do you mean?
Confirmation link not working.
Which confirmation like is this? Is it the one in the email that I send to you?
can any one send this er diagram
The link for the diagram is at the top of the description.
how can i have this source code free please
There’s a link at the top of the description where you can get the source code or SQL scripts.
@DatabaseStar I tried but can't access it🙂
why we remove gender table
It's because we can represent the gender as a category in the category table.
Lets connect
Sure!
@@DatabaseStar OK what's ur social media handle sir
You sound like a Kiwi! 😄
Haha yeah the New Zealand and Australian accents are similar!
I received very very valuable information. So Iʼm glad to see this channel thanks a lot to founder database star
Thanks! Glad you find the videos useful