Fog Creek Software
Discussion Board




Relational databases hate me

So it seems like none of the database-driven applications I want to do can be done cleanly using relational databases. Am I the only one who feels this? Is it just because I don't "get" relational databases? If this were a common complaint I would think there would be more object databases in common use.

For example: I want to create a database of Dublin Core records. All Dublin Core elements are infinitely repeatable and all have optional qualifiers. All elements are also optional. I imagine this can be translated into a relational database, but why should I need a bajillion tables to store one kind of record describing one kind of item? This would be much easier with a simple object database. Does such a thing exist, ideally in the open source world? I haven't found it. I can store the Dublin Core as RDF-XML and store it in an XML database or RDF repository, but why the fuck would I go and do that? I can come up with my own, more sane, XML DTD or schema for storing Dublin Core, but then I'm using nonstandard data formats and native XML database performance is crap anyway.

E. Naeher
Friday, August 27, 2004

"Why should I need a bajillion tables to store one kind of record describing one kind of item?"

Because a "record" does not necessarily equal a single row in a single table. A record is a row in a table and its rows in related tables. I don't know your data structure, but from your description it sounds pretty standard.

Troy King
Friday, August 27, 2004

"relational" is the key feature of relational databases: they are interesting not because of a given element (i.e. row in a table) on its own but because of the relations between rows and tables. If you don't need relations, then there's no reason to use a relational DB. The whole point is to use multiple tables ;) Otherwise you just have a flat file.

And you're right, some problems don't fit the relational model very well. However, an extremely quick look at dublimncore.org looks like it should map fairly well to a bunch of tables. Heck, it's already broken down enough to even be decently normalized. Why not try ER diagramming a bit of it?

Mike Swieton
Friday, August 27, 2004

Life's a bitch, then you die.

Sassy
Friday, August 27, 2004

That's why we get high!!! Seriously, you should limit the information you want to store in the database. Try to find common attributes to all of the records. Any additional random information you have you could probably store somewhere else (provide keys to other tables or data files). Otherwise you might have a database with alot of attributes and NULL data entries which would be a bitch to query.

simm_s
Friday, August 27, 2004

I'd be more upset about the queries that most relational databases support than the format that the data gets stored in.  Those kinds of hierarchical structures would be a snap to work with if standard query languages supported some kind of transitive closure operation (and a few other niceties). 

Check out "Foundation for Future Database Systems: The Third Manifesto" by C. J. Date and Hugh Darwen [1] for some great insight into what a real relational database would be capable of.

And "relation" doesn't refer to relationships between tables.  It refers to the tables themselves.  Each table is a relation.

anon
Friday, August 27, 2004

Sorry,

[1] http://www.amazon.com/exec/obidos/asin/0201709287

anon
Friday, August 27, 2004

Whatever you do, please don't just create a field that contains an XML-serialised version of your object state. I've seen this a lot lately and it drives me up the wall.
Adding any new functionality or even querying the existing data is much more difficult, if not impossible in some cases, and MUCH slower. If you are determined to use a relational database, take the time to do the mapping and understand the logical model, and then translate it into the relevant database entities. Otherwise use flat files.

Aussie Bloke
Saturday, August 28, 2004

Aussie bloke is right.

I've just spent a couple of days pulling gigabytes of XML data from an SQL Server. 

Madness.

Ged Byrne
Saturday, August 28, 2004

> "relational" is the key feature of relational databases: they are interesting not because of a given element (i.e. row in a table) on its own but because of the relations between rows and tables. If you don't need relations, then there's no reason to use a relational DB. The whole point is to use multiple tables ;) Otherwise you just have a flat file.

This is not why relational databases are called relational.
The term relational originates from the mathematical background (mainly set theory) of relational databases where a relation is the equivalent of what most of us call a table.

As a consequence, a database containing only one table is as relational as any other database, provided it is managed by a relational dbms.

Axel Hallez
Saturday, August 28, 2004

"For example: I want to create a database of Dublin Core records. All Dublin Core elements are infinitely repeatable and all have optional qualifiers. All elements are also optional. I imagine this can be translated into a relational database, but why should I need a bajillion tables to store one kind of record describing one kind of item? This would be much easier with a simple object database. Does such a thing exist, ideally in the open source world? I haven't found it. I can store the Dublin Core as RDF-XML and store it in an XML database or RDF repository, but why the fuck would I go and do that? I can come up with my own, more sane, XML DTD or schema for storing Dublin Core, but then I'm using nonstandard data formats and native XML database performance is crap anyway."


Hey, drop me an email.

I was working with a system a couple years back where we implemented *all* of Dublin Core and MODS (another descriptive metadata standard) for a *LARGE* library.

We used an n-level database heirarchy which allowed for very deep (technically infinite) nesting.

KC
Saturday, August 28, 2004

There is nothing that is not suited to the relational database model. If it appears not to be suited, it is because you have designed it wrong. People who prefer flat files to relational databases should take a trip on a time machine back to the 1970's to see the absolute nightmare that it was trying to query hierarchical databases. It was no accident that as soon as the hardware to run a relational database became available in the early eighties all the big companies, and later most middle-sized and smaller companies, moved their data over.

The thing you need to understand with relational databases is that there is no formula for the design. The design depends on the data.

Stephen Jones
Saturday, August 28, 2004

Might want to read some books by Joe Celko, such as:

http://www.amazon.com/exec/obidos/tg/detail/-/1558609202/

Peter
Sunday, August 29, 2004

This http://www.siderean.com/dc2003/701_Poster34.pdf describes an approach to mapping the Dublin Core model to a relational database.

The important thing to realise is that the Dublin Core is a metadata description, a successful relational modelling of this is also going to be a metatada description.  Hence, instead of creating tuples for every possible option and repeated option you create the structure of the option and encode that.

Simon Lucy
Sunday, August 29, 2004

> Whatever you do, please don't just create a field that
> contains an XML-serialised version of your object state. I've
> seen this a lot lately and it drives me up the wall.

Sweet jebus, yes. Hardcoding SQL in source = bad idea. Completely compartmentalizing db logic in stored procs with no developer access = bad idea. Team work and communication between groups = good idea.

So a former co-worker was trying to tell me relational databases are dead, drop that Oracle stuff and use object dbs. What's the current state with that technology? I found his claims pretty hard to believe.

monkeyboy
Monday, August 30, 2004

"pulling gigabytes of XML data"

I thought the total lack of primary/foreign keys on the latest piece-of-shit app to come from one of our contractors was bad.... It definitely could've been worse.

I am Jack's schadenfreude
Monday, August 30, 2004

*  Recent Topics

*  Fog Creek Home