Json to store settings in mysql?

Question

could someone maybe tell me if its a common practise to use json (arrays) in a database to store settings or preferences. Are there any pitfalls perhaps?

Answer 1

You can store JSON in MySQL (or any other database) using a varchar() field for the serialized format.

When you do so, you lose the flexibility of storing data in a table, because the database does not understand the different "columns" in the JSON object. You can get the preferences for a single user relatively easily by retrieving the record for the user and parsing the JSON (presumably at the application level). However, it becomes much harder to efficiently ask questions such as "How many users have option a".

Some databases have native support for JSON (such as Postgres since version 9.2). In MySQL, you can get libraries of functions that have similar functionality (here is an arbitrary example).

If you have just a handful of known preferences that will not change, then create a table with a column for each one. If you have many preferences and users may only have a subset, then these can be stored with one row per preference. Such a UserPreferences table would have columns for:

• User id
• Preference name
• Preference value

This is a type of entity-attribute-value (EAV) data store. This has the flexibility of being able to easily add new preferences. The database can also take advantage of indexes and other techniques for improved performance. However, this structure is not as flexible as JSON.

Answer 2

The question may be better asked on https://dba.stackexchange.com/

That being said, from my experience it's common to store json in a nosql database. For a sql database you'd want to declare the column to be varchar with a fixed size. You can make it very large to accommodate large json (what happens when you have a small json string). You could alternatively make the field smaller (what happens when you have a large json string). You can see how this would be bad for storing arbitrary json. If your json isn't arbitrary then you should create specific fields in your table.

Also think about how you would query against your data if it's a json string.

Answer 3

Doctrine ORM 2 in PHP also allows storing json in DB:

json_array: Type that maps a SQL CLOB to a PHP array using json_encode() and json_decode()

Ref: http://doctrine-orm.readthedocs.org/en/latest/reference/basic-mapping.html

So it's perfectly OK if you need it!

Answer 4

You can use a JSON based database like MongoDB, then it is