Schema – DB Schema for Online Market

schema

I'm just search for advice. I have this db. Every Product type has its own set of characteristics. The Big problem is when someone add new Product type, aslo need create new table with product characteristics. I can see only one solution without DBA, saving all products characteristics in one table but this is not good.

Best Answer

"Entity-Attribute-Value" or EAV design pattern allows you to create new attributes for products without modifying the database structure.

Aaron Bertrand has an excellent article on the advantages and pitfalls of this design over on SQLBlog.org.

A simplified example, based on your requirements:

CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_Products
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductName nvarchar(100) NOT NULL
);

CREATE TABLE dbo.AttributeTypes
(
    AttributeTypeID int NOT NULL
        CONSTRAINT FK_AttributeTypes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AttributeTypeName nvarchar(100) NOT NULL
);

CREATE TABLE dbo.ProductAttributes
(
    ProductAttributeID int NOT NULL
        CONSTRAINT PK_ProductAttributes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductID int NOT NULL
        CONSTRAINT FK_ProductAttributes_ProductID
        FOREIGN KEY 
        REFERENCES dbo.Products(ProductID)
    , AttributeTypeID int NOT NULL
        CONSTRAINT FK_ProductAttributes_AttributeTypeID
        FOREIGN KEY
        REFERENCES dbo.AttributeTypes(AttributeTypeID)
    , AttributeValue nvarchar(100) NOT NULL
);

This will insert some sample data:

INSERT INTO dbo.Products (ProductName)
VALUES ('Oven Trays')
    , ('Cars')
    , ('Vacuum Cleaners');

INSERT INTO dbo.AttributeTypes (AttributeTypeName)
VALUES ('Manufacturer')
    , ('purpose')
    , ('diameter');

INSERT INTO dbo.ProductAttributes (ProductID, AttributeTypeID, AttributeValue)
VALUES (1, 1, 'Farberware')
    , (2, 1, 'General Motors')
    , (3, 1, 'Hoover')
    , (1, 2, 'cookies')
    , (2, 2, 'driving')
    , (3, 2, 'cleaning floors')
    , (1, 3, '12"');

This shows how we get desired output:

SELECT p.ProductName
    , att.AttributeTypeName
    , pa.AttributeValue
FROM dbo.Products p
    INNER JOIN dbo.ProductAttributes pa ON p.ProductID = pa.ProductID
    INNER JOIN dbo.AttributeTypes att ON pa.AttributeTypeID = att.AttributeTypeID
ORDER BY p.ProductName
    , att.AttributeTypeName;

╔═════════════════╦═══════════════════╦═════════════════╗
║   ProductName   ║ AttributeTypeName ║ AttributeValue  ║
╠═════════════════╬═══════════════════╬═════════════════╣
║ Cars            ║ Manufacturer      ║ General Motors  ║
║ Cars            ║ purpose           ║ driving         ║
║ Oven Trays      ║ diameter          ║ 12"             ║
║ Oven Trays      ║ Manufacturer      ║ Farberware      ║
║ Oven Trays      ║ purpose           ║ cookies         ║
║ Vacuum Cleaners ║ Manufacturer      ║ Hoover          ║
║ Vacuum Cleaners ║ purpose           ║ cleaning floors ║
╚═════════════════╩═══════════════════╩═════════════════╝
Related Question