如何将users表连接到角色表（使用表user_roles）？ [英] How to connect the users table to the roles table (using the table user_roles)?

问题描述

我需要创建用于用户身份验证的基本数据库。到目前为止，每个用户都有一个名称，一个密码和一个角色。我发现这在互联网上的某个地方，看起来很有前途：

I need to create a basic database for user authentication. So far, every user has a name, a password and a role. I've found this somewhere on the Internet, which looks quite promising:

create table if not exists users (
  id int unsigned not null auto_increment,
  username varchar(100) not null,
  password binary(60) not null,
  primary key(id),
  unique(username)
);

create table if not exists roles (
  id int unsigned not null auto_increment,
  role varchar(100) not null,
  primary key(id),
  unique(role)
);

create table if not exists user_roles (
  user_id int unsigned not null,
  role_id int unsigned not null,
  unique(user_id, role_id),
  index(user_id)
);

但是...如果我创建新用户，我如何填写 user_roles 表？我有一种感觉有一些自动的方式做到这一点，但我完全不知道（是一个数据库noob :-)）。

But... if I would create new users, how would I fill the user_roles table? I have a feeling there is some "automatic way" to do this, but I have totally no idea (being a database noob :-)). How could I somehow connect a user to a role?

推荐答案

您将首先填充 roles code>表。然后，向 users 表中添加一个用户。然后，从 users 表中获取ID，您需要将其与来自 roles 表中的ID相关联 user_roles 表。像这样：

You would first populate the roles table. Then, add a user to the users table. Then, taking the ID from the users table, you want to associate it with an ID from the roles table inside of the user_roles table. Like so:

---- Users Table ---------
ID | UserName | Password
 1 | Test     | *****
--------------------------

---- Roles Table ---------
ID | Role
 1 | Test_Role
 2 | Another_Role
--------------------------

---- User Roles Table ---------
UserID | RoleID
     1 |      1
     1 |      2
-------------------------------

这是为了多对多关系。它也称为规范化您的数据库。

This is done for a "Many To Many" relationship. It's also called "Normalizing" your database.

这篇关于如何将users表连接到角色表（使用表user_roles）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！