Index for a "JOIN" table in normalization

Question

I have roles for users.

User can have multiple roles. I have a table called users_roles.

I have three columns - id,user,role.

id is an auto-increment column.

So,

Is it a good idea to drop the id column since I never use that in code?
If yes, then what column should be the index for this table? Or should it not have an index at all?

its ok for now if you are not using it but future perspective i suggest you to keep those ids as it provides you flexibility to stretch your code — swapnesh, Jul 19 '12 at 04:52

score 0 · Answer 1 · answered Jul 19 '12 at 04:55

0

I agree that if user is userid then you dont need the id column, userid can be your indexed PK.

If user is the name of the user then you are going to want to keep the id, or create a user_id so that you can have a valid key to index.

answered Jul 19 '12 at 04:55

Johnny B

420
1
5
14

1

I completely missed that line... Keep the id field, I am assuming its an autonumbered integer, in which case the size is max 4 bytes, and if you are concerned about running out of ids you would need to get to 2 billion users before you had to start worrying about running out ids – Johnny B Jul 19 '12 at 05:04

Arun Jain · Answer 2 · 2012-07-20T04:09:53.143

If you are supposing to delete the "id" field then how you will make a relation between user and user_roles table.

It is always better to define a primary key. The default index is created when you define the primary key. And it somehow increases the performance.

Also when you define a foreign key the foreign Key index will also be generated. And hence your table query execution will become faster.

This is your first answer: According to your requirements, for the current time being you can delete the "id" primary key from user_roles table as it is just use as a relationship table between users and roles. But in most of PHP frameworks, this is not a good practice to drop a primary key even in relationship table.

This is your second answer: If you would drop a primary key, then you will have to maintain the indexes on "user" and "role" field as a foreignKey index. And if you are not going to drop a primary key from user_roles table. Then 3 indexes would be generated for "id", "user" and "role" fields. First index will be the primary index and rest two are foreignKey index.

Explicitly defining of more indexes on a table also causes some extra overhead on query execution.

"how you will make a relation between user and user_roles table". I did mention there's a column called `user`. — kapeels, Jul 19 '12 at 17:16

score 0 · Answer 3 · edited May 23 '17 at 10:34

users_roles is a many many link table.

There are at least 2 common approaches to primary keys on many:many tables:

users_roles has its own surrogate Primary key, as in the case here (users_roles.id)
OR, you create a composite key consisting of (user, role), since a user shouldn't be in the same role more than once.

There are many discussions on simple vs composite keys e.g. Why single primary key is better than composite keys?

Note that indexes and primary keys are different concepts. Primary Key is for uniqueness, Index is for performance. (You can have multiple indexes on a table, but only one PK)

If, as you seem to be saying, that no other tables reference user_roles, then you don't actually need a primary key.

If your users_roles table gets large, you will likely want to add an index on the users column, and possibly also the roles table, e.g. if you often search for users in a particular role.

Index for a "JOIN" table in normalization

3 Answers3