As apps are becoming more data driven, SQL database developers haven’t had it easy, as they are challenged to provide database designs that have to cater to all those unstructured data that will be coming their way. But getting this design right is not as black and white as one would hope. It’s a stressful job, but someone has to do it.
I remember a few weeks when I was working on (to correctly put it, consulting on) an app that had more that 10 different tables all of which I (being a junior and all) would have simply created a maximum of 3 tables and got on with my business. But my approach would have hit a dead end when I had to handle all those one-to-many, many-to-one, and many-to-many scenarios which many databases seems to be dealing with these days.
That got me thinking, since each simple request will involve at least two queries to the database (which is something I want to avoid), why isn’t there a system to simply return those two queries into one (in SQL terms, return unstructured data). A noSQL to SQL? Anyone? For example, lets say I want all (or some) of my posts, with all their categories and comments, with one simple query. I know this problem is applicable to even the noSQL databases, as its ‘not practical’ to keep adding nodes to an object, even though those database systems will allow it.
And in comes my suggestion, a database system should be able to think like us, and if the query includes a field that is not in the current table, simply look for a table with that field name, and table join with it using the primary key of the current table. Of course, this needs some more processing, but it would be nice to simply say:
“SELECT post_id, post_title, post_content, categories, comments FROM posts”
and simply return an object/array with the posts data requested…