Re: Database design for a EJB3/J2EE application

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.

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