OODB vs RDBMS

Magnus Lycka lycka at carmen.se
Wed Nov 29 19:26:17 CET 2006


Thomas Guettler wrote:
> Hi,
> 
> most of the time I use ZODB/Durus to store my data. 
> 
> I like it, but I know that it has some weaknesses:
> - only accesible from python
> - I need to code your indexes for fast searching yourself.

There are other features of relational database systems
that I find much more relevant than those, but then I don't
know what your use case is. For high performance, mission
critical, multi-user database systems, where many simultaneous
users actually do fine grained data manipulation, I don't think
there is any competition. Also, I don't know any other solution
that lets you do serious tuning without code changes. Even if the
application is the same, data size and usage patterns might lead
to vastly different performance. Being able to speed up some
operation on the expense of some other operations might be just
the right thing for a certain installation at a certain time.
This can be more or less automatic, depending on your brand and
version of RDBMS.

For a non-critical, single-user system without huge amounts of
data in a structure that fits the relational model, it might not
be worth the effort.

> I think about using a RDBMS for the next project. What I don't like
> about RDBMS: If you need a list of values you need to create a new
> table. Example: If you want to store several email addresses of one
> customer, you need to create a new table.

I think this is a tiny thing when you look at the big picture.
I don't know a lot about the PostgreSQL extensions, but the way
things work in relational database has proven to work very well
for quite some time. Obviously, OODBMS's haven't made any huge
impact, despite two decades of efforts.

The "impedance mismatch" between OO programming and relational
databases is annoying, but it's something we have to (and can)
deal with.

> Since the namespace if tablenames is flat, you soon have so many
> tables, that it is hard to browse them.

The tablename namespace is not flat in SQL. Where did you get this
from? Although not implemented in every RDBMS, the SQL standard has
the concept of a schema, and every table should belong to a schema.
For instance Oracle lacks schemata, but more or less makes up for it
by through the way it implements users. (Tables are owned by users.)

There's just this two level structure though, no abitrary hierarchy.

> Postgres has extensions which allows you to store arrays in a column. Is
> this supported by the python binding? Are there other databases which
> support this?

I don't know about the first question, but regarding the second, none
of the popular ones do as far as I know. PostrgeSQL is a fine RDBMS
though.

By the way, a database is a collection of data, not some software.

> Are there OR-mappers (object relational mappers) which support lists in
> a column?

I think they do, but having a separate class for the email addresses
(if we continue with your example above) has its advantages too. If
the customer has several email addresses (and you feel a desire to
keep track of that) they are probably different in some ways. It's
e.g. likely that you should use one particular address as recipient
when you send mail, not just one at random, or all of them. You might
also realize than not only customers, but also other entities, such
as employees, sub-contractors and authorities have email addresses.
Actually, while you might get more tables due to the first normal
form, your tables might well get leaner, the total amount of columns
smaller, and your over-all datamodel more coherent.

> How is the unicode support of the python bindings to RDBMSs? I don't
> want to convert the results of a query to unicode myself.
> Can you insert unicode strings into a SELECT statement?

As far as I remember, all the bindings I tried returned unicode
objects from varchar and char fields, and always accepted them
as parameters for CHAR/VARCHAR fields.

Remember to always pass parameters properly. I.e. use e.g.
cur.execute("SELECT * FROM T WHERE C=?", col_value) rather than
something like cur.execute("SELECT * FROM T WHERE C="+col_value).
The former will prevent SQL injection attacks, remove the need
to worry about quoting and escaping, and also make performance
better in the major systems.



More information about the Python-list mailing list