Two Primary Key design principles

During my years of database design, I found 2 things that always make my life easier when it comes to primary keys:

1. I recommend to always use Surrogate (otherwise known as "dummy") primary keys. Never assign a "meaningful" column to be the primary key of a table. On Oracle, these are implemented by using sequences and before-insert triggers. In Microsoft's world, SQL Server and MS Access have the auto increment columns. I was once involved in a project where the Id Number was the primary key of an an "Individual" table. The designers took the word of the business people that an individual's Id Number never changes, so they assigned this field to the primary key of the table. Then they built a system of 300 tables, and about 100 of those had a foreign key referencing the above table/field. And of course after a couple of years in production, a case came in where an individual had lost their ID Number and a new one was issued. This was a nightmare: We had to write oracle scripts that dropped the foreign keys, do the update and then re create the foreign keys.
   The rule is, as long as the the primary key column has some meaning to the user, then this column may change and databases do not generally like updates on primary key values. There are ways to do it, I know, but why go through the trouble? Just always use Surrogate keys and you will be free from issues like changing or removing records by primary keys.
   Another reason to use surrogate primary keys is if you have some kind of user interface framework that inserts/updates/deletes rows, usually these frameworks work by identifying rows by primary keys. In case you do not have surrogate/dummy keys in your tables, you user interface can seriously mess up your data. Consider this scenario:
   
   1. User requests to edit record where id number equals '1000'. The system finds it by issuing the appropriate sql where clause ( "WHERE ID_NUMBER = 1000") and shows it on the screen.
   2. The user updates the data on the screen, and then mistakenly enters '1001' in the id number text box. She then submits the changes
   3. The system will then update using where clause "where id number='1001'" since it was changed by the user on the screen, and mistakenly update record 1001 instead of 1000! If you thing this is far fetched, think again! It happened within the first couple of days of UAT for a system I was involved in migrating from Informix to Java 2EE/Oracle. We ended up using the Oracle ROWID instead of the primary key to identify records for the user interface. Probably the best solution to begin with, but had the database use surrogate primary keys then we would not have a problem since user interface users would never see or be able to change the primary key value.
2. I recommend to Never use composite primary keys. Even if it makes sense from a normalization point of view, I always use a a SINGLE sequence/auto increment column to be the primary key of the table. This helps greatly when mapping the database table to some kind of Object Relation Mapping tool. Also, if again you will have to use a User Interface framework to manage your rows, having a sigle primary key column helps to keep the where clauses that identify rows short and easy to read and maintain. As an example, consider the case where you have a many to many relationship between an INDIVIDUAL table and a VEHICLES table. In other words, an individual can own many vehicles, and a vehicle can be owned by many individuals.   Additionally, a vehicle cannot be owned by an individual on the same date.  If we use a composite key, we will end up with something like this:
   
   
   
   
   
   With the above design, in order to identify a record for selects updates and deletes, your WHERE clauses have to include all 3 fields that logically define a unique record: SELECT * FROM VEHICLE_OWNER WHERE VEH_ID=:1 and IND_ID=:2 and OWN_START_DATE=:3
   Alternatively, you can use a single surrogate column for the primary key. You can then add a unique constraint to the table on the 2 foreign keys and the OWN_START_DATE field. Here is the design:
   
   
   
   
   With the above design, your WHERE clauses are kept short and maintanable:   SELECT * FROM VEHICLE_OWNER WHERE VEH_OWNER_ID=:1
   In addition, if in the future you introduce another table which needs a foreign key to VEHICLE_OWNER, you can easily extend your design since you only have a single field as the primary key. You simply add the VEH_OWNER_ID to that new table and create the foreign key. Had you use composite fields as the primary key, then you would have to drop the composite primary key, add a new column, populate values and create a single field primary key.

A case AGAINST using Stored Procedures (and Triggers)

Many programmers are taught (and blindly follow) the "Stored Procedure" principle: If you have to insert/update/delete in a database table from a Java or .NET application, it is desirable to write stored procedures that do the insert/update/delete. The reasons usually given are:

1. Better Performance. The database server parses and compiles the stored procedure text once and re-uses the compiled string for later uses. This applies of course if you are using parametarized sql and you are not (god forbid!) using string concatenation.
2. By using a stored procedure, we allow the database server to also handle business logic, thus relieving the client site from processing.

This above reasons hold water for any RDBMS, albeit Oracle, MS SQL Server, Sybase, etc.

I would argue against using Stored Procedures. The main problems I have with the approach are:

1. The performance argument does not hold water at all. The database server will take the same resources and time to compile the stored procedure sql text, or the plain insert/update/delete sql text. The trick here is to use parameterized sql and not string concatenation.
2. The database is supposed to handle storage, not business processing. It's just not meant for programming logic. That is why we have the "Middleware", to handle our business logic.
3. By moving business logic to a stored procedure, then you are tying your business logic to procedural languages, and you are not taking advantage of Object Oriented programming concepts and techniques.
   The desired approach is to have all your business logic processing in the middle tier, or the client if you are working on a 2-tier system.
4. What about triggers? These are even worse. In my opinion triggers should NOT be used at all in an application. The only reason to use triggers is to get a new sequence number, and this applies only to Oracle. By having your logic dispersed in stored procedures, triggers and client code then you are decreasing the maintainability of your application.

To summarize, my advice is:

• Use plain insert/update/delete statements to update your database from your middleware/client code.
  Do not even create plain stored procedures that just do the insert/update/delete. In the future someone will have the bright idea to start writing business logic code in them. If they are there, they will use them.
• Use Java or any other Object Oriented language to encapsulate and capture ALL of your business logic.

By following the above, you increase the maintainability and extensibility of your application.