0

I have a business object called Schoolyear which has a flags enum at the moment:

[Flags]
public enum VisibleDayOfWeek : int
{
    None = 0,
    Monday = 1,
    Tuesday = 2,
    Wednesday = 4,
    Thursday = 8,
    Friday = 16,
    Saturday = 32,
    Sunday = 64
}

For me this are value objects without identifiers they do not get an extra sql table. This would also be overkill.

Now I thought about saving these visible days (which the user can configure) as int value in the database. It works at the moment but reading/writing in the database and reading/writing those values into a business object and doing integration tests with that objects is a pain.

As I am having a javascript client consuming json data I thought this morning why not save the json array which I get from the browser directly as json string in the database. So the only thing I would have to do is json.parse on client side. And to do integration tests on server side I use the existing json.serialize/deserialize methods from my json library.

The visible days are changed during a year only 1,2 or 3 times not often. Per user there are 5 schoolyear datarow per 5 years maybe not much more. The visible days column would never be queried via sql select. The UI logic is done on client side.

So for me its a good idea to store the json array as json string in the sql database.

What do you think about my new approach? Do you see any negative side effects I have not thought about which I could repent again later.. ?

Improve this question
4
  • SQL does not support JSON natively, so you can not run queries against your JSON data directly. On the other hand, SQL Server supports XML and XML based queries. So, you might want to consider storing converting the JSON to XML for storage.
    – Wagner DosAnjos
    Commented Jan 19, 2014 at 14:43
  • Have you read my WHOLE question or did you just read the title? quote:"...The visible days column would never be queried via sql select"
    – Elisabeth
    Commented Jan 19, 2014 at 17:31
  • If there is a guarantee that there will never be a need to query that data, then just store the JSON on a varchar/nvarchar column. But, I suggest to keep your solution more open to future requirements/enhancements by storing XML instead.
    – Wagner DosAnjos
    Commented Jan 19, 2014 at 21:27
  • The reason why I am so about no changes in the future is because this is a UI concern. Having a single page app all the logic is done on the client :-)
    – Elisabeth
    Commented Jan 19, 2014 at 21:41

1 Answer 1

Reset to default
5

Reasons to not put JSON in a text field in a relational database:

  1. You lose the ability to do queries that depend on the data in the JSON field.
  2. As you do not describe your data to the SQL engine, your application/server code has the responsibility to validate the data and ensure that it can behave if the data becomes corrupted.

Reasons to put JSON in a text field in a relational database:

  1. Efficiency (JOIN can be slow, and if you know that you will never need to query using the data in the field, you do not need to store it in a way that allows that).
  2. Simplify implementation (Just ensure that you check (on the server) that the client gave you a valid JSON string).
Improve this answer
3
  • To your numer 2. I will not do that. The JSON.Stringify function will do that. And if someone uses fiddler to send me bad json the server will anyway not accept the data because he can not serialize it. Thats his job.
    – Elisabeth
    Commented Jan 19, 2014 at 21:56
  • @Elisa I which that I could assume everyone who might read this answer will not do that.
    – ivarne
    Commented Jan 20, 2014 at 11:03
  • In new SQL Server you don't need to worry about JSON validation. You can add JSON constraint that validates that text is properly formatted.
    – Jovan MSFT
    Commented Nov 10, 2015 at 8:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.