Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Database design for a EJB3/J2EE application

Database design for a EJB3/J2EE application

From: Tim Onions <>
Date: Sun, 07 May 2006 09:55:54 +0100
Message-ID: <BAY105-F31D05DBEC2E7853B8E738B99AB0@phx.gbl>

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).

J2EE/EJB is not that new so I'm sure there are listers who design/support applications written in this. So I ask - are the rules I am being told about a must have for this framework (or perhaps just a convenience for the developers)? I would also like any thoughts on the implications of such a design - surrogates everywhere. I can envisage performance and storage issues . Indeed on a previous project we used surrogate keys to reduce the complexity of a set of tables that had 4 or more PK columns each and those tables also had multiple child tables inheriting all the PK columns, and the child tables had further child tables etc. We had to add back in the "missing" FK columns on the child tables after going live as performance of joins suffered terribly (as the SQL had to add in each intermediate table just to get back to the table where a specific column was required in the WHERE clause but that column was no longer on the child table where it would have been if natural PKs had been used and all colunms inherited via the FK).

Many thanks

Tim Onions

Are you using the latest version of MSN Messenger? Download MSN Messenger 7.5 today!
Received on Sun May 07 2006 - 03:55:54 CDT

Original text of this message