How to use dynamic field type in mysql?


Problem :

I want to design a little on-line store. The website should have every detail about products. Since I am beginner I stuck at designing good database design. There are many different products: Cellphone, Laptop, Stove, Bag, etc. Each of these products need different details. I am not going to design many different table for each, So one table (product) going to have all products. But How I manage details? I couldn't find good topic on Google so I started my own poor design. This is my draft design:opps can not post image

  • Product stores product name.
  • Product_category defines type of product, like: mobile or book. each product belongs to one product category
  • Product_category_detail stores product attributes like color, wight, battery life etc. each product_category_details belongs to one product_category.
  • Product_detail keeps values of detail like 3 hour for battery life, or 400g for wight. each of this belogns to one product category detail and product.

I can store all details in Var-char. Is it good? any suggestion! It's better to have type of detail. like varchar for color and int for wight. I am thinking on another field in product_catefory_datail named attr_type so I can change convert in php. Any idea? tnx



Solution :

I had this similar issue while doing a project, and developed tables which solved my problem . Hope this helps you too.

product_table

pt_id,name,category

product_category_table

pct_id,name,desc    

detail_fields

pf_id,field_name

product_detail_fields

pdf_id,pt_id,pf_id,default_value        

product_detail_fields_values

pdfv_id,pdf_id,value,user_id  //here pdf_id is fk of product_detail_fields

If you have multiple users, make use of user_id, else ignore it.


Mysql Tutorials

Mysql Howto..

How to force seting incorrect or nonexistent foreign key index in MySQL

How to export data into a word document, not the SQL Query? VB.NET

How does NOT IN subquery work with NULL values?

How to save the position of drag and drop ( image,button,text ,paragraph ) in php,mysql [closed]

How to get xampp running an older version of mySQL and/or phpMyAdmin

How to get the right output from this SELECT query?

how to get the hierarchical menu from mysql

How to give different values to number of limit people in mysql database?

How to write this mysql query?

Searching using MySQL: How to escape wildcards

MySQL select seems very slow but cannot think how to improve?

how to check the entered value should be greater than previously entered value in DB using mysql query

MYSQL: How can I implement a order by 'Best match' in a query containing `UNION ALL` [duplicate]

How to fetch assoc in mysqli without foreach or while?

how to get php connect to mysql?

How can you speed up making changes to large tables (200k+ rows) in mysql databases?

How do stop form posting to mysql if database contains a specific ID?

MySQL - How to get multiple rows in a single row

How to not include blank fields when updating a MySQL record

How do I look at the schema of a MySQL database on cPanel?

How to pass variable created in php to mysql database?

How to get the running total pay and running balance in mysql?

How do you migrate Amazon RDS from MySQL 5.1 to MySQL 5.5?

How to find the total number of item grouped by month or week in MySQL

How to retrieve JSON data from mysql?

How to compute one column based off multiple tables in mysql

How to make a database updation, creation atomic, and roll back in case of incomplete updation?

How to store one or more column with mysql data in an array

How to count the number of people who choose a particular location?

How to echo strings in PDO MySQL?