Entity Relationship Diagram: Tables query

Question

I am making ERD for Wine warehouse. I have one question about the table so if i have products table and there are different kinds of products like red wine, white wine and rose wine. can i make table to products and then make tables of each wine and link those tables to product table? thanks

Answer 1

Your question is one of database design... yes, you could arrange tables as designed... The question better asked however, is SHOULD you design your database that way. Creating separate tables for each category of product absolutely can be done... the issue is that in order to run any kind of query/report against products in general means you have to solve the problem of including data from multiple tables...

You could create a database view, which is essentially a massive JOIN query... to keep that efficient however you want to be sure to design all of those separate tables nearly identically...

Another alternative would be to consider table inheritance... if the database management system your software is utilizing supports it... essentially your product table would be a parent of all the different product tables... the columns on that table would be inherited by all the child tables... making reporting at the product table level for ALL products a bit easier... You do need a separate list of reports then at the product specific table level then to include columns specific to each of those tables.

So... the answer is YES, you can do as described... the REAL question is SHOULD you.. and the answer to that as I hopefully have illustrated is well beyond the scope of the information you provided in your question. There are MANY factors that will play into how you should organize the data in your database for efficient use.