When to use composite key instead of auto increment

Question

I am creating a database and I am not sure about one of my tables. This table stores the users that are in each game, so one entry per user per game. The user is identified by their user_id which is a 36 character UUID4, and the game is identified by a session_id which is an 8 character alphanumeric string such as "ABK2RXN4". Currently, I have created an additional field just called id which is an integer that auto increments, and I have set that to be the primary key. These are the only 3 fields.

Since the combination of the session_id and the user_id will always result in a unique outcome, should I instead use those two as the composite private key and remove the id field?

Thanks.

EDIT:

Do things change if we have a more complicated table that will require more fields? Take this table for example.

| id  | voter    | voting_for | round    | category_id | acceptable |
|-----|----------|------------|----------|-------------|------------|
| int | char(36) | char(36)   | smallint | int         | boolean    |

This is to do with votes within a game. Essentially after each round, the users in the game will vote on the answers of the other users within the game. So each entry has the user_id of the voter, the user_id of the person they are voting_for, the current round, the category_id of the category within the game and whether or not they agreed with the answer, acceptable. So, you could make a composite primary key using voter, voting_for, round and category_id. Is this any worse just because there are more fields, or should I still use the composite key instead of the existing auto increment id?

Answer 1

That depends.

If the bridge table is going to contain any other columns that are attributes of the relationship between user and game (say, JoinedGameDate or what have you), then the rows should probably have IDs of their own, since it sounds like that's your design pattern, and table structure consistency has some value. Although throwing a UNIQUE index on those two values wouldn't be a bad idea.

On the other hand, if it's truly just a bridge defining the N:N relationship, you could get away with defining a composite key, which would take care of your indexing, and call it a day.

On the other, other hand, the overhead on that one extra auto increment is pretty minimal, so whichever way you go the performance impact of the decision will probably be negligible.

Answer 2

"one entry per user per game" says that PRIMARY KEY(session_id, user_id) would be good for the table entry. (The choice of which id to put first depends on what queries you run the most.)

However, since that adds up to 44 bytes, let's think again.

Each secondary index in the entry table has a copy of the PK silently tacked onto it. This can get bulky if you have more than one secondary index.

Also, if you have other tables that link to entry, they (usually) need a copy of the entire PK, making them potentially fatter.

When we look at the actual performance of all these things, there are several factors, some say (id) would be faster, some say lead to (session_id, user_id) being faster.

So, speed and space would depend on many details that you have yet to present.

Meanwhile, you can speed help both speed and space by shrinking the UUID to BINARY(16), which is 20 bytes smaller. (Strip the dashes, and use UNHEX(); reverse it do go the other way. More details upon request.)

But there is another speed problem with UUIDs. They are very random. So, each insert and each row requested is a random lookup. If you have enough RAM for all tables using UUIDs to be entirely in ram, then this is not a big deal. Otherwise, performance will be bad, and get worse as the tables get bigger.

Shouldn't there be a session_id in the table with voting_for? Will there be any JOINs to/from other tables? Maybe all you need is PRIMARY KEY(session_id, voter, voting_for). That at least clusters the game's information together.

Tips and more discussion:
* UUIDs: http://mysql.rjweb.org/doc.php/uuid
* Many-to-many mapping: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Answer 3

Your bridge table is based on two columns. Both are not nullable and you want a unique constraint on them, so as to have no duplicates.

This makes the columns already a key for the table. "Primary key" is just a name for one such key. You can just leave this as is (i.e. declare the columns NOT NULL and have a unique constraint on them). You can also declare this the "primary key". This would not change anything actually.

You can also decide to add a technical single-column key (an integer ID as you suggest). Then your table would have two unique keys. The latter can be used to reference a row easier from another table, if such exist. Some people working with databases based on technical IDs prefer each table to have such ID, but it's not necessary as long as you don't need the reference. Which key (the ID or the composite key) and if any at all you call "primary" again has no real effect. Typically you would call the ID the primary key.