Is storing the data as json in mysql a good idea?

问题:

I got a scenario to have relational tables like a "section has many elements", so I thought of going for has_many relation model like a section has many elements.

I got some suggestions from our client saying to save the data as JSON inside a column instead of multiple tables.

I am bit confused which approach I should go with.

Suggestions are welcome.

This is the model am proposing

eg : section has_many elements

Table : sections

id  type                
1   page     

Table : elements

id   type   content           section_id
1    text   <p>lipsum lipsum</p>     1
2    image  /images/test.png         1

Client proposal is,

id  type   content             
 1  page    {:text => "<p>lipsum</p>", image => "/images/test.png"}

回答1:

If possible, take a look at a record store solution like MongoDB which will allow you to store your JSON natively. It takes some getting used to, but if JSON will be your primary storage method, this would be a wise choice.

回答2:

Storing data as a JSON in a single database field is generally a bad idea. It means you will not be able to manipulate/query the data using SQL - which kind of defeats the point of storing the data in a database.

I'd recommend a one-to-many relationship between your section table and tables for texts and images. That can be achieved through belongs_to and has_many associations in Rails.

Anyways, creating new tables will only be helpful if you have multiple texts and images for a single section. If you have a single text and a single image for each of your sections (as the example proposes), you'll probably be fine with a single sections table with id, type, text, and image attributes.