The DATAbase is part of the DATA layer, so it should be used for … DATA

I was talking to a friend the other day, and he’s learning some Silverlight (which, as a side note is *really* neat). One of the things he’s doing is building an HTML control, so I asked the obvious questions – why? Well as it turns out, he needs to get some HTML and display it to the user. There’s no Silverlight control which does this (and I don’t think there should be either), and there’s a good reason for it.

The conversation continued, and I soon learned that the database contains HTML, and the UI just gets the appropriate value and displays it. Needless to say, I HATE that. There’s a reason we have different layers, and we should use them appropriately. I’ve seen this happen several times, and it never ends up well.

So why do people do it? Well, there’s some immediate advantages to it:

  1. There’s no recompile necessary for changes: there’s a bug, just have an ALTER statement ready with your fix, or just DROP and add the fixed version.
  2. Shortcuts – if you have some complex data which you need to store, it sure as heck seems easier to store the HTML blob needed, rather than serialize/deserialize things.
  3. Personnel – if you have some SQL-heavy developers in your team, their first instinct is to put it in the database. If you ask a brain surgeon about a medical mystery, he’ll probably see brain damage. If you ask an oncologist, he’ll see cancer. It’s just human nature.

So at this point some of you might ask yourselves, so what’s the problem? Well, there are several issues with using your data layer for more than data:

  1. It doesn’t “scale” – you are now limited to SQL, and for all the power that it provides, there’s LOTS of stuff missing because it’s not what it was intended to do – want to do any advanced XML stuff? need to call a web service? how about some localization?
  2. It’s not strongly-typed – just because you have basic types, tables and columns, it’s not the same thing. Going back to the example at hand – you can store WHATEVER you want in those columns, it’s just ntext or nvarchar. Which brings me to the last point
  3. It’s not secure – so assume my friends writes his Silverlight HTML control, and is now able to get the html blob from the database and just display it, so links work and all. There is absolutely NO way (short of parsing the content – and at that point you have more work to do than you signed up in the first place) to know what that HTML contains. Can you spell Cross Site Scripting vulnerabilities?

I was briefly working on a project a few months ago which was VERY data-heavy (let’s just say that having html in some columns was the LEAST of my worries), and I had to debug a very simple UI issue (hyperlink wasn’t pointing to the right thing). Instead of being a simple fix in a resource file, it resulted in a 2-hour investigation which resulted in editing a stored procedure to fix this.

So as a last word of advice – please, please, please stop abusing you database and use it for what it’s meant – storing your data. Do it in a smart manner (normalize your data, use indexes, use stored procedures instead of dynamic sql etc.) and get the most out of it. But do not store everything in there just to save some code higher in the stack. Else, you’ll have major headaches down the road, and pulling stuff out of the database is not as easy as you’d imagine.