SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Is this good or bad table design? (JSON strings)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

waveform
Yak Posting Veteran

Australia
89 Posts

Posted - 12/09/2013 :  00:51:59  Show Profile  Reply with Quote
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?

Edited by - waveform on 12/09/2013 00:55:02

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/09/2013 :  01:44:16  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waveform
Yak Posting Veteran

Australia
89 Posts

Posted - 12/09/2013 :  01:51:23  Show Profile  Reply with Quote
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.

Edited by - waveform on 12/09/2013 01:52:51
Go to Top of Page

uberman
Posting Yak Master

159 Posts

Posted - 12/09/2013 :  03:42:41  Show Profile  Reply with Quote
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.
Go to Top of Page

waveform
Yak Posting Veteran

Australia
89 Posts

Posted - 12/09/2013 :  03:57:35  Show Profile  Reply with Quote
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?

Edited by - waveform on 12/09/2013 04:31:29
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/09/2013 :  06:07:39  Show Profile  Reply with Quote
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 difference
As MSSQL have lots of functions which MySQL doesnt support
Its 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000