The story of compound keys

Short story: I needed a table that could visualize the relation between two other tables ( services & portfolio ). So I set up a “join table”; a table that has two columns: service_id and portfolio_id. Each column contains a foreign key ( services.id and portfolio.id ).

Now, what you normally do is add a third column “id” as primary key. But in this case it can be a recipe for ( a programmer’s ) disaster. The column id would always contain a unique value, but the combination of the two other columns service_id and portfolio_id will not be unique. And thus you could run into a situation where you have multiple rows that hold the same relation:

Continue reading The story of compound keys