I am starting to design a typical product based MySQL database but I keep running in circles on how to design the tables. I have many types of productsJeansTshirtsDressesWhich have many of the same attributesSizePriceColourBut some of the products have specific attributes, like length or logo or package.Do I make a separate table for every product even though many of the columns are the same, or do I make a general product table and then have some sub-tables for specific products?
Database Design for Inventory
database;database design;data modeling
Do not make a table for every product. This problem has been solved many ways. Try this:Make a product (or products) table, put your common product attributes in the product table, then make an attribute table and a productattribute table, something like:attribute---------attributeIdattributeNameattributeDescriptionproductattribute----------------productIdattributeIdattributeValueThen you can assign arbitrary attributes to your products.You can take the schema a little further if you like, using:attribute---------attributeIdattributeNameattributeDescriptionattributeType // [float, int, varchar, etc.]productAttribute----------------productIdattributeIdattributeValueFloatattributeValueIntattributeValueVarCharThis technique complicates your schema a bit, so be aware of what you are getting into if you decide to use the extra columns to store values by data type.An alternative that might just suit your needs a little better would be to use a NoSQL Store like MongoDB for your products. Then you can store the attributes you need for each type of product without resorting to the complexity of a relational database schema.