I've recently inherited a project, and have been looking through the database to see how it's put together. In many of the tables, there are columns which store data in JSON format - data which, if I'm correct, would be better off in a separate table. For example:

See how the Address data is crammed into a JSON text column. I haven't seen this sort of thing done before. Is it acceptable database design? Personally, I would have placed the Address columns in a separate table (or just added columns to the same table, if all users needed Address info anyway).

Is using JSON strings like this acceptable? If so, what are the basic rules to determine when to use it?

Its not. It will violate the 1st Normal Form itself which indicates attributes should be atomic ie lowest possible independent unit which cant be divided further. So you need to parse these values and get individual pieces and store it in separate columns. Otherwise DML operations (INSERT/UPDATE/DELETE) on these column values can be real pain.

Thanks Visakh. The guy who did this is a computer science graduate - is there any reason why he'd design a system (which is meant to have a substantial public user base) in this way? I've met him, he's very smart and was hired by Amazon after that job.

I don't want to get on his bad side by asking him directly :), so would you have any insight as to why he may have done that? It wasn't a one-off either, he did it in several tables. Very odd.

At a guess I'd say that this is data that he doesn't really care about but which needs serializing and serializing for display/use (maybe on a website), pure store and display.

E.g. the address data needs to be stored and echo'd back into the address fields, maybe used on a printed label , but beyond that he doesn't really care what that data is; nor feel the need to find all active professionals in the city of "Bristol".

If he (/you) actually do care about the data at level beyond store and display then you might need to think about taking action.

I thought there may have been some MS SQL magic which allows searching/operation on JSON data, but sounds like that's not the case.Edit: Oops, actually it's MySQL, not sure if that makes a difference?

It will make a lot of differenceAs MSSQL have lots of functions which MySQL doesnt supportIts not impossible to do the serach in T-SQL but my point was creating such logic would be inefficient and design like this is against database design principles like normailsation.