Are there significant cons to deeply nested types in DB columns?

Question

I'm trying to evaluate the pros and cons of structuring my data model in various ways, and I'd like to know if I'd be shooting myself in the foot by stuffing rather complex nested data types into individual columns of a SQL table.

For example, let's say I want to serialize an array of structs (or even worse, array of hashes of hashes) and save it in a single column. It'd most likely be a somewhat nested JSON dictionary. Something like:

user_id | user_related_data_blob
---------------------------
1       | { .... }

The obvious cons I can immediately see are data coupling, in case some data isn't quite tightly related to the user. There's also the size of each retrieved row, which might make fetching from the web rather slow, especially if most of the data isn't even immediately necessary by the client. Doing SQL by those columns also becomes rather complex (and probably not indexable) unless there's special tech in place to support it.

Pretty much the only upside, and it might be significant depending on the context, is that if you don't want to create a complex schema and spend a lot of time making sure that all the constraints are sane and that you have a lot fewer moving parts. For something like a quick prototype, it might even make sense.

Am I missing anything here? Is there a rule of thumb out there in the SQL world that states that you should never nest data in an individual column? Any good guidelines?

I don't see any significant PROs, except if your goal is to disable any usefull features of an RDBMS. — iDevlop, Jan 20 '13 at 08:50
Seeing your other comments, I got the feeling that you are definitely not on the RDB planet. So, to avoid "acid" replies, I suggest you remove the SQL tag from your question. — iDevlop, Jan 20 '13 at 08:56
Why was this question tagged as mongodb and nosql, when your question is about SQL tables? — Philipp, Jan 20 '13 at 13:25
I clearly didn't explain my question well enough and I think we should close it. I'll pose it more precisely next time. — Alexandr Kurilin, Jan 20 '13 at 20:49

score 2 · Answer 1 · 2013-01-20T08:48:17.480

2

Is there a rule of thumb out there in the SQL world that states that you should never nest data in an individual column?

First normal form for a start. Perhaps you're after a NoSQL solution?

edited Jan 20 '13 at 08:48

answered Jan 20 '13 at 08:42

I'm using Parse right now for the prototyping I'm doing. Parse is essentially a web wrapper over MongoDB. What were you getting at regarding NoSQL? – Alexandr Kurilin Jan 20 '13 at 08:49
@glitch from the wording of your question I assumed you were using a traditional RDBMS. If you are after rdbms vs nosql here's a link http://stackoverflow.com/q/4160732/1914530 – Jan 20 '13 at 08:55

score 0 · Answer 2 · answered Jan 20 '13 at 11:22

A major problem is you will likely need to retrieve your data into another environment to be able to update it effectively, unlikely able to access it in the most effective ways. Postgres, for example, has a json data type and some functions for retrieving and storing data, including type verification, but you still need to retrieve the object from the database, serialize it in your code, and then access it how you want.

With the many varieties of data stores if you must store data in specific formats I'd seek out options designed around that. Mongo and Couchbase, for example, are great engines for storing JSON encoded data allowing you to access a JSON object as its natural type.

Going back to Postgres for a second, it does provide the row_to_json() function which will return table rows formatted as JSON. If you can easily map your JSON data to a flat table structure, this may offer some possibilities. Of course it does not help with unstructured or semi-structured data.

score 0 · Answer 3 · answered Jan 20 '13 at 13:42

Storing blob data in a SQL database is a bad idea, because SQL doesn't understand it and doesn't provide useful tools to work with it.

there is no way to search or filter BLOB data
there is no way to retrieve just parts of the BLOB, it's all or nothing
most SQL databases aren't built internally for handling BLOBs

You could find a dirty workaround using string operations in SQL to get some limited access to the content of the blobs, but these will be very, very slow and cumbersome to implement and maintain.

But when nested documents are the best way to express your data, you should consider using a document-oriented database like MongoDB or CouchDB which provide the tools to search, filter and modify individual fields of documents.

Are there significant cons to deeply nested types in DB columns?

3 Answers3