It would be nice to have here some knowledge about relational database design. At least some good books for reading. Also some practical examples what to do, or what to avoid in this area. I think Marko Korkiakoski has made some teaching material on this topic in Helsinki. If so, is it available also for us?

-- Jarmo

For me there are three types of db-app design:

  • Ad hoc sql queries mostly behinde some kind of DAO (Data Access Objects) layer.Best when the application is simple, but most used.
  • ORM (Object Relation Mapping: castor,hibernate,ejb-cmp,..). Best used when developers aren't db experts and the database maps well on the object structure.
  • Stored procedures, used to hide the database structure. Best used when the application is nothing more than a view on the database (or large part) or most of the logic is inside the database. Requires high db skills, probably the best design for most applications.

So when your application is simple use adhoc queries, keep queries in a property files.

When you only need a persistence mechanism for your objects use ORM (a real timesaver).

When you need to build an enterprise application using the company db best to use stored procedures. which are actually the business methods.


I think Marko was supposed to make a presentation, but AFAIK there is nothing available yet. I would definitely like to see some tips here as well. I have added some questions below. I think everyone is on holiday...

(Yeah, Marko was on holiday until August. I understand something is happening at the end of August.)


Do the length of VARCHAR columns matter?#

Does it matter whether you pad the column sizes to nearest-multiple-of-8 (that is, is 500 better than 512 or vice versa?)

It depends about DBMS, for example Oracle's VARCHAR2 stores only the actual length used, some dummier DBMS's will store always at maximum length. I think that most useful databases aren't that dummy...


Actually at least with Oracle 8i (8.1.7) when you specify a column to be for example of type VARCHAR2(50), it doesn't guarantee that the column can store 50 characters. This depends on the character encoding used by the database, and in the case of multibyte encodings (such as UTF-8) a character can occupy two, three or even more character "slots".

That is, setting a VARCHAR2 column size guarantees only that the specified number of bytes, not characters can be stored.

This caused some confusion and head-scratching at our current project, and so far the only feasible (although a pretty rough) solution was to double the column sizes to be sure that enough characters can be stored. In theory, even that is not enough, but for real-world usage it should do just fine.

-- --

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-7) was last changed on 08-Aug-2005 20:46 by ChristophSauer