Hi,

I would like to know from experience which method is generally used.

Suppose I have the table

Table1 (A, B, C, D, E) primary key (AB)

Is it better to transform the above table into

Table1 (ID, A, B, C, D, E) unique (AB), primary key(ID) where id is surrogate, thus in my opinion eleimination the possibility of data redundancy.

Also when other tables are associated via a foreign key constraint for example only the ID (logically) needs to be passed as opposed to the composite values (although oracle uses a unique row id anyways).

Any comments appreciated

Cheers

There's an endless debate over whether or not to use surrogate keys I'd prefer to avoid. And one of the reasons you stated for surrogate use isn't true from a normalization standpoint in that columns A,B should always uniquely identify an instance in table1. These are called natural keys and might be choosen from several possible "candidate keys" that could provide uniqueness. Candidate keys not choosen to be the PK are by definition "alternate keys". The argument that using a surrogate key makes joins easier for the RDBMS to handle really doesn't hold water any longer as most vendors have overcame this issue many years ago. I believe Richard Yevich, several years ago said it best, and I'll paraphrase, "You usually never need a surrogate key until you really, really need it." A legitimate use of a surrogate key would be employeeIdentificationNumber, as using any combination of attributes about an employee could contain dups, even Social Security Number occasionally dups. There are certain Data Warehousing and Data Mart demensions that lend themselves to surrogate key use as well.

It is OK to add a surrogate "ID" column, as long as you also create a UNIQUE constraint on (A,B) as you did above. There are pros and cons:

Pros:
1) Shorter foreign key for child tables
2) Simpler join conditions to child tables
3) The "natural key" (A,B) can be updated without having to cascade the update to the child tables.

Cons:
1) Additional, meaningless column in table
2) In child tables, the foreign key cannot be understood by a user unless you join to the parent table to get (A,B)

My preference would be to use the natural key, but only if it is not excessively long and will never be updated.

Some people use surrogates in every table they ever create; I would not do that. For example, suppose we have tables Employee (emp_id PK) and Department (dept_id PK) and there is an intersection table Employee_Department( emp_id, dept_id, start_date, end_date ). I would make the PK for this as e.g. (emp_id, dept_id, start_date) - I would not add a surrogate like "emp_dept_id".

Of course, emp_id in this example probably is a surrogate key, because people are notoriously difficult to uniquely identify without one (2 people can have exact same name, date of birth, etc.).