Let's assume you have a table
groups and a table
item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to another group.
When trying to decide on a primary key for the
item table, what should I use?
Should I make up an artificial global serial key like this:
CREATE TABLE items ( item serial PRIMARY KEY, group integer NOT NULL REFERENCES groups(group), );
... or should I rather use a composite key and per-group item serial like this:
CREATE TABLE items ( group integer NOT NULL REFERENCES groups(group), item integer NOT NULL, PRIMARY KEY(group, item) );
The reason why I'm leaning more towards the second solution is that the post URL will always show the group and item, so it makes sense to have both of them as the composite primary key. In case of the first solution, the URL contains superfluous information because the group ID can already be deduced from the item ID alone. The URL structure is given, however, and cannot be changed.
The disadvantage of the second solution is that you have to manage a per-group serial (i.e. each item integer should start from 0 for each group).
What's better in terms of best practices, normalization and performance? Or is it simply a matter of taste?