Database design for smartphone product and its features

Question

I want to create database for smartphone and its features with feature category

    Feature Category
------------------------
|id     name
------------------------
|1      Processor
|2      Memory
|3      Display
|_______________________


Features
------------------------------------------------
|id     name                feature_category_id
------------------------------------------------
|1      Process Speed           1
|2      Process Technology      1
|3      RAM                     2
|4      Screen Size             3
|_______________________________________________

Product 
----------------------------------------
|id     name                Description
----------------------------------------
|1      Moto G4
|2      Iphone 6s plus
|3      Lenovo K4
|______________________________________

now i want to store features for particular product for e.g iphone 6s plus product have ram = 2GB, Screensize = 5 inch etc.... But i want best solution for database design.

score 1 · Accepted Answer · edited May 13 '20 at 17:18

Based on your original design; you need a many-to-many relationship added to the ones you already have:

CREATE TABLE features_x_products
(
    product_id integer NOT NULL REFERENCES product (id),
    feature_id integer NOT NULL REFERENCES feature (id),
    PRIMARY KEY (product_id, feature_id),

    value varchar(200) NOT NULL,
    unit varchar(50)
) ;

This creates an EAV model (Entity - Attribute - Value). It's got many caveats. The one is mostly dislike is the fact that you have to store all values as text (or varchar), unless you have various value columns (i.e.: value_number decimal ; value_text varchar(200) ; value_timestamp timestamp ; ...), but then you'll also need some extra meta-information on your attributes to find out wich column to retrieve.

References and alternatives:

Side Note: The concept of best design doesn't really exist. There are good solutions to specific problems. Some solutions are good for many different problems, others only a apply to very few ones. Depending on how you're going to query the database, whether you access the database using from one programming language or another, might make using JSON specially appealing, or completely awful. Same applies for EAV.

Database design for smartphone product and its features

1 Answers1