Mysql table design two primary keys vs one

Question

I was wondering which of following is better design.

I've got these tables

users
ID | NAME

Categories
ID | NAME

Which one is better, this:

users_to_categories
CAT_ID | USER_ID

In this case CAT_ID and USER_ID are primary keys

or one primary key

users_to_categories
ID | CAT_ID | USER_ID

only ID is primary key

If it will help you in your searching, the two terms don't refer to "one" primary key or "multiple" primary keys, as a table can only have one primary key. You are looking for the difference between a "synthetic" primary key and a "composite" primary key. — Tripp Kinetics, May 28 '14 at 20:16
Either is fine. Having a single ID is easier to develop against as it's less coding on inserts, updates deletes. However, it takes up more space, adds more overhead, and isn't a key people would logically search on. So the only real reason to have ID on the users_to_Categories is to simplify coding when doing updates so you have a unique 1 column key. So this is really a choice on development simplicity. I'll tell you from a reporting and troubleshooting standpoint non-intellegent keys such as this cause additional headache as you have to always go back to source table. — xQbert, May 28 '14 at 20:18
Because in the current simple example, the `users_to_categories` table only exists to denote a relationship between the `users` and `Categories` tables, you don't need to have a separate field just to identify the relationship. This changes if you are going to be "hanging other tables off of the relationship" or if you are going to have multiple versions of the relationship between the records in the two tables, if you catch my drift. — Tripp Kinetics, May 28 '14 at 20:18

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

I assume that you are trying to create a many-to-many relationship between the two tables with a third table titled Users_to_Categories. If so, then CAT_ID and USER_ID would be foreign key pointers to the primary keys in Users and Categories.

All that being said, I would refer to the answer given in this thread (SQL - many-to-many table primary key). I agree with the answer given here, saying that there is really no advantage to creating a new auto-increment primary key when the combination of the CAT_ID/USER_ID can serve as a primary key itself.

edited May 23 '17 at 11:50

Community

1
1

answered May 28 '14 at 20:19

alondo

141
1
4

There are multiple answers given to the other question. One of the answers brings up an important caveat. If it's purely a "link" table, with no other attributes, the composite key is appropriate. But if the "link" table has attributes and/or there are other tables that have foreign key references to the "link" table, then a single column surrogate key can actually provide some advantages. Both the "composite key" and "surrogate key" approaches are workable. I implement a composite key _only_ when I'm certain there will be no FK references and no attributes (aside from "housekeeping" cols) – spencer7593 May 28 '14 at 21:09

score 0 · Answer 2 · answered May 28 '14 at 20:39

Altought both designs are fine, I recommend go for one single field for key approach.

It's easier for doing queries.

It's also easier when you want to change some details records from a header o parent table to another, just change the parent foreign key.

Mysql table design two primary keys vs one

2 Answers2