Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
waveform
Yak Posting Veteran
93 Posts |
 Posted - 2013-12-09 : 00:51:59
|
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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-09 : 01:44:16
|
| 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.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2013-12-09 : 01:51:23
|
| 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. |
|
|
|
uberman
Posting Yak Master
159 Posts |
Posted - 2013-12-09 : 03:42:41
|
| 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. |
|
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2013-12-09 : 03:57:35
|
| Ah, cool yes that's probably what it is. Thanks.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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-09 : 06:07:39
|
quote:
Originally posted by waveform
Ah, cool yes that's probably what it is. Thanks.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.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Is this good or bad table design? (JSON strings)