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?

"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
– T.J. Crowder, Commented Sep 8, 2016 at 5: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
– Strawberry, Commented Sep 8, 2016 at 6:57
Possible duplicate of input unknown number of fields into mysql - best structure for this? — e4c5
– e4c5, Commented Jan 11, 2017 at 9:02

Oscar Pérez · Accepted Answer · 2016-09-08 05:47:16Z

1

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.

answered Sep 8, 2016 at 5:47

Oscar Pérez

4,3971 gold badge20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related