CODEWITHSUNDEEP
mysqlsqldatabaseormrelational-database
Chathuranga Jayawardhana
Chathuranga Jayawardhana

Reputation: 416

Store multiple data tables in single database table

Im designing MySql database for store multiple products such as computers,mobile phones,pen drives....etc.each product has different features such as

**Computers has**
Processor
Ram
HDD
Monitor Size
....etc

**Mobile phone has**
Display Size
Network type
OS
Internal Memory
....etc

**Pendrive has** 
Capacity
USB Version
....etc

And i have to store unlimited number of products, instead of creating separate tables for each product how to create database structure to store these information on one or fixed number of tables(data tables + mapping tables).(i think Wordpress store data in this kind of format, it uses few tables and store any number field related to post/category within those tables).any help/idea to solve this problem would be appreciated.

Upvotes: 2

Views: 2751

Answers (3)

TlmaK0
TlmaK0

Reputation: 3906

You can select one approach used by some ORM like hibernate or doctrine

https://docs.jboss.org/hibernate/orm/3.5/reference/en/html/inheritance.html

http://docs.doctrine-project.org/en/2.0.x/reference/inheritance-mapping.html

Your selection depends on your data and how you will use it.

If you never will get a list with different kind of devices at once you should use different tables.

If you plan that all "devices" should share some fields like "ref", "name", "description", "price", "weight", etc, you should use one table because take a simple list with all kind of devices will be easy and low cost, but the table can grown if you plan to add more types of devices and properties.

If you plan that this fields will increase the table then you should split concrete properties of each type of device in different tables and use a discriminator field in the devices table that will allow you to make joins.

If the properties of the devices will grown a lot (or dynamically), then you should consider to use a table with fields: attribute and value. In this case the queries will be more heavy.

So basically you can do:

computer->

mobile->

pendribe->

or:

devices-> type, price, name, processor, ram, internal memory, capacity, usb version

or:

devices->type, price, name

computer-> processor, ram

mobile-> internal memory

pendribe-> usb version

or:

devices -> id, price, name

attributes-> id, id_attribute, value

Upvotes: 0

Temitayo
Temitayo

Reputation: 812

Consider this Create three table product, feature, product_feature and maybe product_photos

Product database will be 

pid, p_name, p_description, p_price, ...
insert query 
INSERT INTO (p_name, p_description, p_price, ....) VALUES(?,?,?,...)

feature table will 

fid, f_name, f_description, ...
insert query 
INSERT INTO (F_name, F_description, ....) VALUES(?,?,?,...)

now the product_feature table will be

id, pid, fid 
query for one product
// say a product Id is 1
INSERT INTO (pid, fid) VALUES(1, 10) 
INSERT INTO (pid, fid) VALUES(1, 15
INSERT INTO (pid, fid) VALUES(1, 30)

where pid and fid are foreign keys with relations, phpmyadmin can do that for you you can then add a product with multiple features

then maybe the photo table 

foto_id, photo_name, photo_path ....

use InnoDB for all the tables

Let me know if you need further help

Upvotes: 2

Jag
Jag

Reputation: 291

You need to design a table in such a way that it covers all attributes for all products. This would help you insert any type of product in to that table. But, keep in mind that if there are 4 products with 10 different attributes, you end up creating 40 columns and might use only 10 to insert data at any point of time.

Upvotes: 0

Related Questions