Should I Use a Composite Key or a Single-Column Primary Key in my Entity Relationship Diagram?

Question

I am designing an Entity Relationship Diagram (ERD) for a database that includes a table with variants annotated by different tools. In this table, each variant is annotated by a specific tool, and the combination of the tool and the variant uniquely identifies each row.

The table currently has two columns: database_tool and variantID. I am considering two approaches to define the primary key for this table:

Composite Key Approach: Using both database_tool and variantID as a composite primary key.

Single-Column Key Approach: Creating a new column, say database_tool_variantID, and using it as a single-column primary key.

I am seeking advice on which approach might be more beneficial for my database design. Are there specific advantages or potential pitfalls that I should be aware of with either approach, particularly in terms of query efficiency, database maintenance, and scalability?

Any insights or experiences with similar scenarios would be greatly appreciated.

Answer 1

Are there specific advantages or potential pitfalls that I should be aware of with either approach...

Depends on how your system operates.

One generically true potential pitfall of a composite key with meaningful values such as database_tool, is the risk of that value changing. Perhaps database_tool has a value one day called Saw and a year later that value needs to be renamed to Table Saw. This causes 2 problems:

1. Once you rename that value in the source database_tools table, the combination table with variants will be immediately compromised from a data integrity perspective (and anywhere else that key value was being referenced).

2. To fix said data integrity issues, you'd have to update every reference to the old value of Saw in every table referencing it. This is not necessarily a performant way to go about things.

Of course this entirely depends on if such a scenario is possible and is why immutability is a good property to consider when choosing a primary key. If instead, you had a single table of database_tools that had a unique ID value that was part of the composite key, then that would avoid the above scenario when the value needed to change.