1

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.

1

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.

2
  • thanks for answer but you give me answer based on my design so i want to know whats better database design for this type of database. Jul 2 '17 at 10:03
  • I've already given you pointers to more than one alternative; plus, explained why best isn't a meaningful concept.
    – joanolo
    Jul 2 '17 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.