Re: Database design for a EJB3/J2EE application

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Mon, 8 May 2006 00:54:06 +0300
Message-ID: <6e49b6d00605071454q3c1cee5cg@mail.gmail.com>

I'm only pro surrogate keys and to my mind they have at least following benefits:

1) each table will have its own <prefix>_id
2) they will be always the same type
3) so frontend as well as I in SQL*Plus screen can use consistent

framework to access all tables in the SAME way 4) they wouldn't have any natural meaning and even with the most immutable natural keys in the world there is possibility that they'll change and I really don't want to change all connected FK columns 5) I'd really don't want to write joins including more than one column for each pair because even for one pair developers tend to create Cartesian joins sometimes (once I had to make some reports based on a parent->child->grandchild->great-grandchild tables joining 3 columns for 2 tables - arrgh)
6) if you always need access parent (columns) from great-great-..-grandchild you can easily add derived FK's (or even other columns i.e. make denormalization), but I'd say these are specific cases not ordinary needs. Of course these (at least mostly) have to be identified even BEFORE you generate any CREATE TABLE scripts.

To my mind UK's are just for natural keys and potential waste of properly cached sequence created surrogate key space in table and index is far outweighed by potential waste of 3 column FK, potential change of it, nonconsistent access of tables both from developer minds and Oracle side.
I do have bad experience with natural keys as PK but haven't such with surrogate PK. Probably that's only my distinction ;)

Gints Plivna

2006/5/7, Tim Onions <att755_at_hotmail.com>:
> Dear All
>
> I have been offered the opportunity to design the database for a large OLTP
> business critical system which will be architected using J2EE and EJB3
> framework. I am told by the architects that the DB must comply to some
> strict rules because of the framework. They say that every table must have a
> PK (fair enough) and that the PK cannot be compound - ie must always be one
> single column!Where no natural business PK exists with a single column a
> surrogate key MUST be used.
>
> This goes against my design philosophy and although I do use surrogate keys
> I do so on a table by table basis as the design requires (eg natural key is
> not immutable, natural key has many columns, more than 3, and is used as a
> FK on many other tables etc - checked up on askTom last night and this seems
> to be his general approach to DB design although J2EE/EJB was not part of
> his discussions).

--
http://www.freelists.org/webpage/oracle-l

Received on Sun May 07 2006 - 16:54:06 CDT