Database Design - Multi-Category Products with Properties
I am developing a basic inventory system for a vendor.
They have many different product categories.
Each product category has many different properties.
A - x1, x2, x3, a1, a2, a3;
B - x1, x2, x3, b1, b2, b3, b4;
C - x1, x2, x3, c1, c2;
Laptop - Make, Price, Quantity, Processor, OS, Hard drive, Memory, Video Card etc
Monitor - Make, Price, Quantity, Size, ContrastRatio, Resolution etc
Server - Make, Price, Quantity, Processor, OS, Memory, Netowrking etc
Design1: Different tables for each category.
Design2: general table, property table.
What's the best approach?
source to share
Definitely not need to unnecessarily multiply outlines (Occam's rule, y'know). The standard way to organize a many-to-many relationship is simply an intermediate table:
Products
--------
ProductID
Categories
----------
CategoryID
ProductCategories
-----------------
ProductID
CategoryID
Its simple request and industry best practice.
source to share
Without knowing more about your domain, I would tend to use design 2. This will limit the number of tables and make queries on various properties of several categories more readable and efficient.
Design 1 is worth considering if you have a small number of categories that are static and each has different properties. If so, you can still use design 2 by creating a property dictionary table. Here you will have a table containing property / property pairs. Then your category table can contain category id, property id, property value columns. This works well if all properties are of the same data type, but can be awkward if they don't.
source to share
The second option will require a join for each query, and the first option will make the query harder because you will have multiple tables and now you need to decide which one to query. I would prefer the second option because it is ultimately easier to develop the application (but I can be lazy).
source to share
A common design is a generic table for the most common properties and then a child table for each category with specific properties for that category.
Alternatively, you can use an entity value structure, but this is generally not recommended as it is difficult to query and does not scale well.
One thing you will need to do that people often forget is to keep the purchase price in a record for an item in inventory (one of which I would consider general properties). If you rely on the price in the product table, it will update as the price changes, but for accounting purposes, this is not what was paid for the item. This can be really bad when you are checking in or during tax time!
source to share