Why should I create a MySql table containing index only?

Question

I'm following a Java Spring tutorial to learn some basic information about the secure login in a web app. In this tutorial, the author has created 3 MySql table to manage the authentication:

CREATE TABLE `roles` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `role` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `login` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `user_roles` (
  `user_id` int(6) NOT NULL,
  `role_id` int(6) NOT NULL,
  KEY `user` (`user_id`),
  KEY `role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The table "roles" contains the user role (for example "Admin", "User" etc...). The table "users" contains the user login and password.

So, I can't understand why the table "user_roles" has been created! The relation between "roles" and "users" is one-to-one, so I could insert an index for these 2 tables and delete the "user_roles"...is it right? Why should I need to join the tables "users --> user_roles --> roles" instead of "users --> roles" ?

Thanks in advance :)

users --> roles works only for a on to many relation ship. The other case is for many to many relationship — Jens
@Jens thanks! So, if I would create a one to one relationship, should i integrate the column "role" in the users table? In this case, the tables roles and user_roles are not useful anymore... — user3449772

Rick James Rick James · Accepted Answer · 2015-08-16T00:24:24

For a 1:1 relationship, have just one table. (There are exceptions, but I don't see any reason for such here.)

If you have many users in each role but a user is in only one role, then add role_id to the Users table. This is 1:many. You may need INDEX(role_id).

If a user can have many roles and each role can have many users, then you need many:many. And this would be the optimal way to write the 3rd table:

CREATE TABLE `user_roles` (
  `user_id` int(6) NOT NULL,
  `role_id` int(6) NOT NULL,
  PRIMARY KEY (`user_id`, role_id),
  KEY         (`role_id`, user_id)
) ENGINE=InnoDB DEFAULT;

In some sense, that is an index-only table, since the PRIMARY KEY contains all the fields.

The (6) in INT(6) is meaningless. In particular, it does not give you 6-digit integers, it still gives you 4-byte signed integers up to 2 billion. Perhaps you should use MEDIUMINT UNSIGNED for values of 0..16M. Or SMALLINT UNSIGNED for 2-byte values of 0..64K.

Why should I create a MySql table containing index only?

1 Answers