title: Double Primary Key Design
body: <p>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,</p>
  
  <pre><code>DepartmentA    0001
  DepartmentA    0002
  DepartmentB    0001
  DepartmentB    0002
  </code></pre>
  
  <p>are valid data in my table. But </p>
  
  <pre><code>DepartmentA   0001
  DepartmentA   0001  
  </code></pre>
  
  <p>is <em>not</em> valid. </p>
  
  <p>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.</p>
  
  <p>What I am thinking is that I create the following tables</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>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 <a href="http://en.wikipedia.org/wiki/Object-relational_mapping" rel="nofollow noreferrer">ORM</a>, i.e., they are not fully supported and one has to write customized queries for it. </p>
  
  <p>Is the above design the best, given my scenario? </p>