DB Schema for online market

Question

I'm just search for advice. I have . 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.

I think I can guess how adding a new product type can be a problem but I can also imagine scenarios where it's not. Why is it a big problem in your case? Could you elaborate on that a little in your question? Thank you. — Andriy M, Feb 15 '18 at 10:00
The problem is any creation of tables in production. As programmist I can easily create any tables, but in production db has to have hard sceleton. — pogody lea, Feb 15 '18 at 11:04

score 2 · Accepted Answer · answered Feb 15 '18 at 14:19

"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 ║
╚═════════════════╩═══════════════════╩═════════════════╝

DB Schema for online market

1 Answers1