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 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.
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
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.
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.
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.
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
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.
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
@@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
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
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?
This is one of the videos to Design a Database. Appreciate your time to make this video.
Glad it was helpful!
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!
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
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!
this is the best video I have seen before. thanks a lot !!!!
Wow thanks! I’m glad you like it.
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/
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!
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!
This is the best video I have seen before!
Thanks! I’m glad you liked it.
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.
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 so much. You explained it very nicely.
Glad it helped.
Excelent video, regards from Caracas, Venezuela
Thanks a lot!
Thank you, very useful to me.
Glad it was helpful!
this is top content!! thanks a lot!!
Glad you like it!
nice in depth explination. thanks for your efford :)
Glad it was helpful!
appreciate this comprehensive video!
Glad it was helpful!
This is so helpful! Thank you!
Glad it was helpful!
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.
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.
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.
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.
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.
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
So much high quality information
Thanks! Glad you liked it.
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.
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
This is so amazing
Thanks! I’m glad you like it.
Sir it is possible to include product category as an attribute of product ?
Yes, you can join to the category table for this.
I want you say Thank you. 🙏🙏🙏
You're welcome!
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.
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
Thank you so much
You're most welcome
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!
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/
what name software for create database design like this video?
It’s called LucidChart.
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
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/
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
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
What use application for database design
It’s called Lucidchart
Thank you
You’re welcome!
Please design how to design permission based system there are mutiple actors .each actor has some permission
Good idea, I can do that.
I love you. you are the best.
Hahah thanks!
Confirmation link not working.
Which confirmation like is this? Is it the one in the email that I send to you?
thanks bro
No problem!
Where is the direct link to Schema?
What do you mean?
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!