Double Primary Key Design

Question

My database has a table that keeps track of the department and user id. The catch here is that while the department and user_id column can have duplicates, the combination of them can't. This is to say,

DepartmentA    0001
DepartmentA    0002
DepartmentB    0001
DepartmentB    0002

are valid data in my table. But

DepartmentA   0001
DepartmentA   0001

is not valid.

The combination of department and user_id forms a unique identifier of a record. And in another table, I need to use this unique identifier to track the users activities, like what's the time they enter a building, what time they leave the building etc.

What I am thinking is that I create the following tables

CREATE TABLE user (
 user_id INT( 4 ),
 department VARCHAR( 25 ) NOT NULL ,
 combined_id int(4) ,
 UNIQUE ( combined_id ) ,
 Primary key(user_id, department)
 );



CREATE TABLE user_activity(
combined_id int(4),
activity varchar(25),
Foreign Key (combined_id) references user(combined_id)
);

So I am thinking about using a double primary key for my purpose. This, IMHO, is the best way to guarantee data integrity. But from what I know using double primary key can be quite hard to work with ORM, i.e., they are not fully supported and one has to write customized queries for it.

Is the above design the best, given my scenario?

Answer 1

I would use surrogate primary keys, and put a unique constraint on the combination of those 2 fields that should remain unique.

That is, I would not consider to be my employee-number as a primary key. This facilitates a lot of scenarios.

So, this is what I would do

CREATE TABLE Employee
(
  EmployeeId INT ,
  EmployeeNumber VARCHAR(4),
  DepartmentId INT,
  EmployeeName
)

CREATE TABLE Department
(
   DepartmentId INT,
   DepartmentName
)

In the Employee table, EmployeeId is the Primary Key. This column just contains a number, which has no meaning in the problem-domain. It has just an administrative meaning in the database (uniquely identify an Employee).

The EmployeeNumber contains the 'business / domain number that is used to identify the employee). Note that I've made this field a VARCHAR field, since you LPAD that number with zeroes in your example. So in fact, it should not be a numeric field. :)

Next to that, there's also a DepartmentId column in the Employees table which is a foreign key to the Department table. In the Employee table, you should put a Unique constraint on the EmployeeNumber/DepartmentId fields. (So, one constraint on the combination of both columns).

Then, the User_Activity table can just look like this:

CREATE TABLE User_Activity
(
  EmployeeId INT,
  ActivityId INT
)

(I know that the CREATE TABLE statements are not really actual correct SQL Statements, but this is just for illustration purposes offcourse).

Answer 2

I would create your primary key as a composite on User_ID and Department, and have another field in that table that is an identity/autoincrement field (also with a unique index on it), and use THAT value as your FK reference to other fields.

This assumes that you don't delete and recreate user/department pairs very often (since you'll break those linkages with the subordinate tables each time) in which case it might make more sense to use the composite key as your FK reference.

Answer 3

I would suggest having the surrogate identifier as the primary key, and the compound user_id and department key as an alternate unique key.

Answer 4

Just create separate primary key, and create new unique index for (user_id, department).