StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStoring JSON in database vs. having a new column for each key
text
Body
copied!<p>I am implementing the following model for storing user related data in my table - I have 2 columns - <code>uid</code> (primary key) and a <code>meta</code> column which stores other data about the user in JSON format. </p> <pre><code> uid | meta -------------------------------------------------- 1 | {name:['foo'], | emailid:['foo@bar.com','bar@foo.com']} -------------------------------------------------- 2 | {name:['sann'], | emailid:['sann@bar.com','sann@foo.com']} -------------------------------------------------- </code></pre> <p>Is this a better way (performance-wise, design-wise) than the one-column-per-property model, where the table will have many columns like <code>uid</code>, <code>name</code>, <code>emailid</code>. </p> <p>What I like about the first model is, you can add as many fields as possible there is no limitation. </p> <p>Also, I was wondering, now that I have implemented the first model. How do I perform a query on it, like, I want to fetch all the users who have name like 'foo'?</p> <p><strong>Question</strong> - Which is the better way to store user related data (keeping in mind that number of fields is not fixed) in database using - JSON or column-per-field? Also, if the first model is implemented, how to query database as described above? Should I use both the models, by storing all the data which may be searched by a query in a separate row and the other data in JSON (is a different row)? </p> <hr> <h2>Update</h2> <p>Since there won't be too many columns on which I need to perform search, is it wise to use both the models? Key-per-column for the data I need to search and JSON for others (in the same MySQL database)?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload