Webdevotion.be

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:


id | service_id | portfolio_id
-------------------------------
1 | 5 | 3
2 | 2 | 1
3 | 5 | 3


The solution is the use of a compound key: use only two columns and make them both primary key. This will force the relation to be unique, always.


service_id | portfolio_id
--------------------------
5 | 3
2 | 1


I’m suprised I never had to use this before, but I remembered a reference in the “Agile Development with Rails” book.