title: When should I use a composite primary key?
body: <p>ETA:  My question is based upon keeping an optimal database.  What is the difference in database performance/size between having an all composite primary key for <strong>ProjectUserBooleanAttribute</strong>, which would presumably have indexes for <strong>PUAT_Id</strong> and <strong>UserID</strong> and a non-composite table using an auto-increment PK but then having indexes for both <strong>PUAT_Id</strong> and <strong>UserID</strong>?  From further reading it seems if I went with the non-composite approach I would have to create a unique index on the those two columns.  Would I still need to create indexes on those two columns? If so, doesn't this essentially means every column in that table would have it's own index?  </p>
  
  <p>Is this the quintessential dilemma of database size (indexes) vs performance? </p>
  
  <hr>
  
  <p>So I have the following entities I wish to create</p>
  
  <ul>
  <li>Projects</li>
  <li>Users </li>
  <li>ProjectUserAttributeTypes</li>
  </ul>
  
  <p>In this simplified example, all of my ProjectUserAttributeTypes are going to be <strong>boolean</strong>, so I'm only showing the <strong>ProjectUserBooleanAttribute</strong> table.</p>
  
  <p>Let's say I want to create two boolean ProjectUserAttributeType called <strong>Silver</strong> and <strong>Gold</strong>.  I would simply create two rows in <strong>ProjectUserAttributeTypes</strong>.  Now if I want to assign a user as having that attribute, I would add a row to the <strong>ProjectUserBooleanAttribute</strong></p>
  
  <p>A DBA has warned me off from using composite primary keys in general for performance reasons.  However in this instance I fail to see what I gain by NOT using composites.  In both cases I will need to make sure that <strong>ProjectUserBooleanAttribute</strong> has non null and unique values for all columns.  I will surely need indexes as well.</p>
  
  <p><strong>NOTE:</strong> My ultimate objective is to be able to query my database and find all users that have certain attributes combinations.  I would join the tables to filter by project, then use where clauses to filter even further.  A few examples:</p>
  
  <ul>
  <li>(GOLD OR SILVER) </li>
  <li>(GOLD XOR SILVER)</li>
  <li>((GOLD OR SILVER) AND NOT (BRONZE))</li>
  </ul>
  
  <p><strong>COMPOSITE PK</strong></p>
  
  <p><img src="https://i.stack.imgur.com/Vg3sc.png" alt="Compsite ERD"></p>
  
  <p><strong>VERSUS NON-COMPOSITE PK</strong></p>
  
  <p><img src="https://i.stack.imgur.com/52nNs.png" alt="No Composite ERD"></p>