Why Store Your Data In A Relational Database?

Jun 29, 2006 sql data patterns suggest edit

With Ted Neward’s recent post on the morass that is Object-Relational mapping, there has been a lot of discussion going around on the topic. In the comments on Atwood’s post on the subject, some commenters ask why put data in a relational database. Why not use an object database?

The Relational Model is a general theory of data management created by Edgar F. Codd based on predicate logic and set theory. As such, it has a firm mathematical foundation for storing data with integrity and for efficiently pulling data using set based operations. Also, as a timeless mathematical theory it has no specific ties to any particular framework, platform, or application.

Now enter object databases which I am intrigued by, but have yet to really dig into. From what I have read (and if I am off base, please enlighten me) these databases allow you to store your object instances directly in the database, probably using some sort of serialization format.

Seems to me this could introduce several problems. First, it potentially makes set based operations that are not based on the object model inefficient. For example, to build a quick ad-hock report, I would have to write some code to traverse the object hierarchy, which might not be an efficient means to obtaining the particular data. Perhaps an object query language would help mitigate or even solve this. I don’t know.

Another issue is that your data is now more opaque. There are all sorts of third party tools that work with relational data almost without regards to the database platform. It is quite easy to take Access and generate a report against an existing SQL database or to use other tools for exporting data out of a relational database. But since object oriented databases lack a formal mathematical foundation, it may be difficult to create a standard for connecting to and querying object databases that every vendor will agree on.

One last issue is more big picture. It seems to me it ties the data too much to the current code implementation. I have worked on a project that was originally written in classic ASP with no objects. The code I wrote used .NET and objects to access the same data repository. Fortunately, since the data was in a normalized relational database, it was not a problem to understand the data simply from looking at a schema and load it into my new objects.

How would that work with an object database? If I stored my Java objects in an OO database today, would I be able to load that data into my .NET objects tomorrow without having to completely change the database? What about in the future when I move on from .NET objects to message oriented programming or agent oriented programming?

Ultimately, the choice between an OO database and a relational database really depends on the particular requirements of the project at hand. However the thought of tying an application to an OO database at this point in time gives me reason to pause. This could lock me into a technology that works today, but is superseded tomorrow. On several projects I have worked on, we totally revamped the core technology (typically ASP to ASP.NET), but we rarely scrapped and recreated the database. The database engine might change over the years (Sql 6.5 to Sql 7 to Sql 2000 to Sql 2005), but the data model survives.

Found a typo or mistake in the post? suggest edit

Comments

16 responses

Dare Obasanjo • June 29th, 2006
http://www.25hoursaday.com/...
Simone Chiaretta • June 29th, 2006
when will subtext move to NHibernate or similar ORM?
Jeff Atwood • June 29th, 2006
> It seems to me it ties the data too much to the current code implementation
Bingo!
Which is more important: the data in a simple, understandable format.. or the developer of the month's idea of an object model?
Haacked • June 29th, 2006
Simone: Not sure. Still studying up on it.
Scott • June 29th, 2006
I did a commercial project with an OO database (ObjectStore, to be exact) in 1999. As we versioned the product, the data migrations became, and required more and more custom C++ code. We were on Oracle by the year 2000. :/ It's a good idea from an academic standpoint, but I just don't see it ever becoming mainstream.
Other Scott • June 29th, 2006
Back in 2001 I looked at a few object databases. I was working for a Java house at the time so Gemstone was one I remember looking at. Aside from some of the transactional capabilities, what is the difference between using an object database and serializing your objects out to flat files?
Simone Chiaretta • June 29th, 2006
Phil: I think moving to an ORM will make SubText compatible "out-of-the-box" with many DB engine.
And will simplify the current ObjectProvider + DbProvider implementation.
BTW: I've quite a good knowledge of NHiberanate, and a friend of mine (in the ugi group) is a sort of NHibernate MVP.
Sam • June 29th, 2006
Worrying about what lasts longer, data vs. application always seems like such a strawman to me. It's not like db4o (or any other, but it's the simplest IME) is going to lock your data into a vault.
It's just my opinion, but if the day or two spent dumping
your objects to Xml so they can be imported into an RDBMS for a replacement system is really that big of a concern, then you're a lot better of a developer than me. Personally, such a simple, down-right tedious task, that I could estimate with my eyes closed makes me pretty comfortable.
Of course if you think Sun/MS/et all time-bomb their frameworks, and that you won't have the option to bust open Snippet Compiler or such when you finally need to make the migration then you might have a point.
I'm pretty comfortable thinking that I'll be able to run .NET2 for a good long time though... and if not, the db4o source-code is free. I'm sure I could figure out the storage format well enough to dump my data without too much effort.
Of course, I'm not trying to say an OODBMS in general is a good idea. I think for most .NET applications that maintain a database of a few gigs in size, db4o is a faster, more fun way to develop (than SqlServer), but I had a good deal more trouble trying to get Matisse or FastObjects to work the way I wanted, with syntax that wasn't every bit as cumbersome as ADO.NET so YMMV.
tod hilton • June 29th, 2006
I've been using relational databases much longer than OOP so I'm more comfortable with them, but your points give me further reason to pause. I particularly like the independent aspect of not tying your app so tightly to the data.
Dare's article looks interesting also...
Haacked • June 29th, 2006
Sam, it isn't just an issue of *timebombed* frameworks. It's also the issue to being able to access your data from other means.
In my example, classic ASP was still being used. But I was writing administrative apps in ASP.NET against the same data.
I also implemented MS Reporting Services to generate reports on the same data. If I wanted to, I could take any of the myriad of business intelligence tools and point them to our database to gather more information. Also, it wouldn't matter which language or coding platform these extra tools were implemented in.
It's rare for a business not to have the need at some point to be able to slice and dice their data.
Thomas Wagner • June 30th, 2006
Several years ago I did a project with CNET data systems in which a Swiss company with a few Russian programmers created a business that used OO db's to describe any product sold by a company on the internet. They literally had 5 teams entering and cross checking data. So a manufacturer like HP would send its product catalog with part numbers. It gets entered into the db as something like PC - Form Factor. PC-Accessories . PC-Keyboard. Whatever was being sold had a form factor and attributes. The system was very efficient. The company then offered its service to any ecommerce vendor who would send their entire catalog of part numbers in and get back a beautifully designed very searchable RDBMS catalog of products - not OO but RDBMS. Companies like Office Depot and Staples use a similar approach. Anyway the whole thing was fantastic and the OO db proved a great barrier to entry for any competitors trying to reverse engineer the system based on a copy of the rdbms catalogue. I dont think anyone has been able to crack their business.
Daniel • June 30th, 2006
I've tinkered with db4o, and I'm pretty sure it addresses some of those. For example, you can share stored objects between java and .NET, given they have similar schema. Granted another language could come along, and you'd be in trouble, but in theory they could add support.
Also, it doesn't just serialize the data- there's indexing, etc. built in. It also has some LINQ-like features for querying, etc. which apply set theory to the data.
I'm not 100% there yet, but I think we should definitely re-think the problem some.
Cold Chilli • July 4th, 2006
Once you allow objects into your data, you now need proprietary application to translate(Import/Export) the data. Rather than SQL.
Every time I've had XML or serialized objects in the database or simplify the storage; I've been later burned by the need to query the data.
Therac-25 • March 13th, 2007

Random thoughts from someone more about ideas...
I've been looking around at object databases for the last little while, and nothing I've found even comes close to being useful as a replacement for an RDBMS in that space.
One of the things I think that's not useful is the idea of an object database as simply a way to serialize objects to a different place. Unfortunately, that seems to be what the majority of object databases are.
What we need in this case is a way to store data in a form that maps naturally to an object representation, but is independent of language. A few of the things that would be key factors are:
* The ability to represent inheritance
* A persistent connection scheme the doesn't have the overhead of SQL or another relational representation so that data can reasonably be accessed on demand.
* A query mechanism that maps well to the object representation in a given language -- this being probably the biggest challenge in the general case. SQL needs to be tossed.
* A way to handle locking of object records -- perhaps a "check out" paradigm?
* Most importantly, a way to express operations on sets of objects independent of their physical storage or language of origin.
The thing is, you might be able to do this in an RDBMS (I know PostgeSQL supports table inheritance), but you'd need to replace the SQL interface with something that can talk about objects, not about relations.
What I think is damaging the idea is how closely most object databases tie themselves to the representation of Java-like objects. I, anyway, want a database that can store object state in a way that I can recreate an isomorphic object in any language environment. Quickly.
Anand • April 26th, 2007
I have registration page. In that full data i want save in my data base . i want to create total codeing javascript or php: which code i want give in that my html page?
RL Deran • July 31st, 2010
Look at InfinityDB. It is in-between RDBMS and OODB. The data model is the 'ItemSpace'.