Re: Database design for a EJB3/J2EE application
- From: "Thomas Day" <tomday2@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Mon, 8 May 2006 16:01:08 -0400
Way back when Jared and I had a discussion on this list as to the use of
natural vs surrogate foreign keys. I'm a firm convert to the surrogate key
camp. Many good reasons given above. Most important reason to me is that
the real world does not always match the logical world and, though logically
your natural key should be unique, in the real world there's always some
exception. Somebody fat fingers the data input if nothing else.
The problems that I've had with Object Oriented applications have to do with
the behavior of the application server (I've seen where they'll do a 'select
(*) on table_name' to see if any data exists in the table before they try to
select the data that they want. Why they think that this is a good thing is
beyond me.). Also, usually the data layer will be done with a minimum of
effort. The programmers will suck up all the columns in anytable that they
access and sort out the data that they want programmatically. It makes for
unnecessary network traffic. Also, they'll be paranoid over the "state" of
the database and want to do a read-after-write so that the state of the
presentation layer will match the state of the database. They don't trust
Oracle to send an error message if any CRUD fails. They'll want to go and
look for themselves.
Make sure that they bring all the data from Oracle error messages out to the
presentation layer. Too often they will drop it at the data layer and give
the user some really informative message like 'UNEXPECTED ERROR - CALL
DBA'. As if that's supposed to be of any use.
Also, they will all believe that all it takes to be a DBA is to know SQL.
They'll get a couple of SQL books and pretty soon they'll start telling you
how to design your database. They'll also want you to drop all your
constraints, so that they can load any old data that they want, to make
their development easier. Resist these attempts. A clean database design
is essential to a clean framework design.
Other related posts:
