sql - Best way to add extra fields to a sql database

Question

I want to know what is the best way to manage extra fields on a SQL database (MySQL 5.7 in my case) for example I have the products table with the fields

create table product(   
 id int auto_increment,
 product_name varcha,r
 description varchar,
 other json,
 primary key(id)
);

But in the future if I want to add extra fields like weigth or height I will store the info at other field as json format. Is a good idea do this? or is better create a new column for each field. and when is a good choice to store data as json format?

[alter table](http://dev.mysql.com/doc/refman/5.7/en/alter-table.html) and [ETL](https://en.wikipedia.org/wiki/Extract,_transform,_load) as you go — Drew, Sep 08 '16 at 05:44
*"and when is a good choice to store data as json format?"* That's a **completely** different question from the rest of your question. (And the answer is: When, and only when, you have a *very* good reason you cannot store it normally.) — T.J. Crowder, Sep 08 '16 at 05:47
You don't need a very good reason. You just need a reason. One reason might be that you have no intention of performing a search against that criteria. — Strawberry, Sep 08 '16 at 06:57
Possible duplicate of [input unknown number of fields into mysql - best structure for this?](http://stackoverflow.com/questions/41569614/input-unknown-number-of-fields-into-mysql-best-structure-for-this) — e4c5, Jan 11 '17 at 09:02

score 1 · Answer 1 · answered Sep 08 '16 at 05:47

Well, it all depends on your needs.

If the structure of these "extra" fields will change from row to row, use something lika a json encoded text could be a good idea as long as you never need to do an SQL operation over them (as, for example, a WHERE condition).

Anyway (and as long as your extra fields structure is variable from row to row), I would recommend to use another table where you can store all of the extra atributes, for example:

 CREATE TABLE extra_attributes(
     id integer not null primary key,
     product_id integer not null,
     attribute_name varchar(64) not null,
     attribute_value varchar(128)
 );

And then create a foreign key to products table.

If all of your products have the same extra fields, the simplest solution will be to add the needed columns to tha product table.

sql - Best way to add extra fields to a sql database

1 Answers1