Primary key in "many-to-many" table

Question

I have a table in a SQL database that provides a "many-to-many" connection.

The table contains id's of both tables and some fields with additional information about the connection.

CREATE TABLE SomeTable (
  f_id1 INTEGER NOT NULL,
  f_id2 INTEGER NOT NULL,
  additional_info text NOT NULL,
  ts timestamp NULL DEFAULT now()
); 

The table is expected to contain 10 000 - 100 000 entries.

How is it better to design a primary key? Should I create an additional 'id' field, or to create a complex primary key from both id's?

DBMS is PostgreSQL

Answer 1

This is a "hard" question in the sense that there are pretty good arguments on both sides. I have a bias toward putting in auto-incremented ids in all tables that I use. Over time, I have found that this simply helps with the development process and I don't have to think about whether they are necessary.

A big reason for this is so foreign key references to the table can use only one column.

In a many-to-many junction table (aka "association table"), this probably isn't necessary:

• It is unlikely that you will add a table with a foreign key relationship to a junction table.
• You are going to want a unique index on the columns anyway.
• They will probably be declared not null anyway.

Some databases actually store data based on the primary key. So, when you do an insert, then data must be moved on pages to accommodate the new values. Postgres is not one of those databases. It treats the primary key index just like any other index. In other words, you are not incurring "extra" work by declaring one more more columns as a primary key.

My conclusion is that having the composite primary key is fine, even though I would probably have an auto-incremented primary key with separate constraints. The composite primary key will occupy less space so probably be more efficient than an auto-incremented id. However, if there is any chance that this table would be used for a foreign key relationship, then add in another id field.