Re: Database design for a EJB3/J2EE application
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: att755@xxxxxxxxxxx
- Date: Mon, 8 May 2006 00:54:06 +0300
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@xxxxxxxxxxx>:
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).
--
//www.freelists.org/webpage/oracle-l
Other related posts:
