数据库表 - 足够规范化吗？
这是我为管理用户帐户设计的两个表。
create table if not exists users (
id int unsigned not null auto_increment,
username varchar(100) not null,
password binary(60) not null,
first_name varchar(100) not null,
last_name varchar(100) not null,
role_id int unsigned 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)
);
我认为我需要规范化第一个表，例如将第一个表拆分为某种 user_info(first_name, last_name, ...) 和帐户（用户名，密码，role_id）。我遇到的问题是我非常不确定为什么需要这样做，因为我无法真正解释为什么它不在 3NF 中。
编辑
一名用户只能拥有一个角色（管理员、高级用户、用户）。
Here are two tables I designed for managing user accounts.
create table if not exists users (
id int unsigned not null auto_increment,
username varchar(100) not null,
password binary(60) not null,
first_name varchar(100) not null,
last_name varchar(100) not null,
role_id int unsigned 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)
);
I think I need to normalize the first table, e.g. splitting the first table into some sort of user_info(first_name, last_name, ...) and account (username, password, role_id). The problem I have is that I am very uncertain of why I need to do this, as I can't really explain why it isn't in 3NF.
EDIT
A user can only have exactly one role (admin, poweruser, user).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
仅当一个用户可以有多个账户或者一个账户可以有多个用户时，才需要将用户信息和账户信息分开。如果用户与帐户的关系始终是一对一，那么您就按原样标准化。
有时，如果很少使用第二个表中的列，则以一对一关系分隔列是有意义的。然而，在这种情况下，似乎两个表都会被填充，因此分离这些列没有任何好处。
You only need to separate the user information and account information if a user can have multiple accounts or an account can have multiple users. If the user-to-account relationship is always 1-to-1, then you're normalized as is.
Occasionally it makes sense to separate out columns in a 1-to-1 relationship if the columns in the second table will be used rarely. However, in this case, it seems as though both tables would always be populated, so there's nothing to be gained by separating those columns.
仅当允许有用户 ID 和用户名但没有相应的名字和姓氏时，才进一步分解用户表。否则，看起来您的表已经处于 5NF 状态。
Decompose the users table further only if it's allowable to have a user id and username without a corresponding first name and last name. Otherwise it looks like your tables are already in 5NF.
我不是 SQL 专家，但这个表对我来说看起来非常标准化。您应该规范化表以节省空间：
如果您有一个列，例如
role，并且您有 20 个用户和 5 个角色，每个角色使用 10byte，您将拥有20 * 10bytes = 200bytes< /代码>。但是，如果您对表进行规范化（正如您已经完成的那样），则只需要
5 * 10bytes = 50bytes作为角色名称，5 * 1byte = 5byte作为 id在角色表中为 20 * 1byte = 20byte 在用户表中为 id。200bytes未标准化50bytes + 20bytes + 5bytes = 75bytes标准化形式。这只是一个非常不完整且基本的计算来展示背景。
I'm not a SQL Expert, but this tables looks very normalized to me. You should normalize a table to save space:
If you have a column, like
roleand you have 20 users with 5 roles, each roles uses 10byte, you will have20 * 10bytes = 200bytes.But if you normalize the table, as you have done it already, you will only need
5 * 10bytes = 50bytesfor the role name,5 * 1byte = 5bytefor the id in the role table and20 * 1byte = 20bytefor the id in the user table.200bytesnot normalized50bytes + 20bytes + 5bytes = 75bytesin normalized form.This is only a very incomplete and basic calculation to show the background.