Is it okay to store JSON array data in a MySQL database?

Question

I have a unique situation. Users of my site can submit articles for other users to review, however they can restrict who can review the articles by age and by country. My issue is that instead of having 250 records (1 for each country linking to the article) I thought it fitting to store all 250 countries (or whichever countries they want to be visible to other users) in JSON format in a text field in the database. This way I would only need one record per article. I'm not sure if the performance will suffer terribly? The site will handle between 1-2 million users and the number of articles submitted for review will be rather large as well. The only "processing" that would be done is each user's country is stored in the database and it would be checked against the country array for an article to see if that user is allowed to review that article.

What do you guys think? Am I over-thinking 250 records for each article is a lot?

Answer 1

I think that storing the data in a lookup table is perfectly acceptable. It gives you much more freedom in the future if something changes, and as long as you nicely index the tables, performance won't be sufffering too much.

Mysql easily handles data that is billions of records. Yes, you will need to ensure that you look after your data integrity - but adding a column to a lookup table versus changing an object that is stored in every single record suddenly seems much easier.

Just make sure that you are keeping the data properly - as in you aren't repeating information that doesn't have to be repeated. Keep countries in one table, and a simple ID in the lookup table that references it.

Answer 2

In short I would say that storing Json Data within a column in a Relational database is fine if you are not going to be querying data based on that column.

If you will be needing to look up data based on that column there would be a huge performance hit to having to parse the json prior to excluding data thus this would be a no no.

We ran into this issue at my job on a smaller scale and storing json of the properties in the database has worked well to not increase the complexity of the database for non searched properties.