Composite Primary Keys : Good or Bad?

by

There is no conclusion that composite primary keys are bad.

The best practice is to have some column or columns that uniquely identify a row. But in some tables a single column is not enough by itself to uniquely identify a row.

SQL (and the relational model) allows a composite primary key. It is a good practice is some cases. Or, another way of looking at it is that it’s not a bad practice in all cases.

Some people have the opinion that every table should have an integer column that automatically generates unique values, and that should serve as the primary key. Some people also claim that this primary key column should always be called id. But those are conventions, not necessarily best practices. Conventions have some benefit, because it simplifies certain decisions. But conventions are also restrictive.

You may have an order with multiple payments because some people purchase on layaway, or else they have multiple sources of payment (two credit cards, for instance), or two different people want to pay for a share of the order (I frequently go to a restaurant with a friend, and we each pay for our own meal, so the staff process half of the order on each of our credit cards).

I would design the system you describe as follows:

Products  : product_id (PK)

Orders    : order_id (PK)

LineItems : product_id is (FK) to Products
            order_id is (FK) to Orders
            (product_id, order_id) is (PK)

Payments  : order_id (FK)
            payment_id - ordinal for each order_id
            (order_id, payment_id) is (PK)

This is also related to the concept of identifying relationship. If it’s definitional that a payment exists only because an order exist, then make the order part of the primary key.

Note the LineItems table also lacks its own auto-increment, single-column primary key. A many-to-many table is a classic example of a good use of a composite primary key.

More Related Contents:

  1. How to create multiple one to one’s
  2. Relational Data Model for Double-Entry Accounting
  3. What are the options for storing hierarchical data in a relational database?
  4. Is there any rule of thumb to construct SQL query from a human-readable description?
  5. How to implement a many-to-many relationship in PostgreSQL?
  6. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  7. What are best practices for multi-language database design? [closed]
  8. Strings as Primary Keys in SQL Database [closed]
  9. Are soft deletes a good idea? [duplicate]
  10. Database Design for Revisions?
  11. A beginner’s guide to SQL database design [closed]
  12. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  13. Storing money in a decimal column – what precision and scale? [closed]
  14. single fixed table with multiple columns vs flexible abstract tables
  15. Primary key or Unique index?
  16. How to design a database for User Defined Fields?
  17. How to Implement Referential Integrity in Subtypes
  18. Database EAV Pros/Cons and Alternatives
  19. Max length for client ip address [duplicate]
  20. Name database design notation you prefer and why?
  21. MySQL – how to front pad zip code with “0”?
  22. Does an empty SQL table have a superkey? Does every SQL table have one?
  23. Use email address as primary key?
  24. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  25. SQL: Using NULL values vs. default values
  26. What does ON [PRIMARY] mean?
  27. What is the best way to implement soft deletion?
  28. Temporal database design, with a twist (live vs draft rows)
  29. Historical / auditable database
  30. Single or multiple databases

Leave a Comment