MySQL - 数据库设计 - 为用户和个人资料分别创建表格

Question

根据我在网上阅读的内容，我了解到如果可能的话最好将数据分成更多的表，因为这样可以提高访问速度。

现在我有一个表，其中存储着用户名、密码和加入日期。这是我的表格外观：

'user'
'user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_username VARCHAR(80) NOT NULL,
user_password VARCHAR(255) NOT NULL,
user_join_date INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (user_id) ');

我正在尝试创建一个名为“profiles”的新表，我想在其中存储名字、姓氏、电子邮件、年龄和性别。我认为需要一对一的关系，因此应该使用user_id，但我不确定如何实现。

我是否应该创建另一个名为"profiles"的表？

profiles_id
first_name
last_name
email
age 
gender

还有一个问题，关于关系？例如：

user_profiles
----------
user_id
profiles_id

这样对吗？用户配置文件的SQL查询应该是怎样的呢？

谢谢。

- mihai121

---

你的第一句话是不正确的。你能引用一下你在网上看到的内容吗？有时使用多个表格会更好，有时则不然。 - Gordon Linoff

Answer 1

不要分割表格，只需将新列添加到现有的user表中。您可能会发现，根据实际查询和使用模式，拆分表格是一个好主意，但在您获得这种数据之前，请保持简单。

如果必须创建个人资料表，请勿创建user_profiles表。这将允许m对n关系，这可能不是您想要的。更好的方法是在profiles中创建一个简单的user_id列。事实上，它可以是外键和主键，以确保每个user行只有一行profile（尽管通过拆分表格，仍然可能存在没有个人资料的用户）。

- acfrancis

---

我喜欢这种方法，因为它避免了每次查询用户信息时都要进行一次JOIN。然而，我想知道这在数据库规范化规则方面有多好。对此有任何评论吗？ - user3308043

Answer 2

通常，当一个用户可以拥有多个配置文件或者一个配置文件可能属于多个用户的情况下，你会创建一个像你所描述的 user_profiles 的 关联表。

正如你所说，这里存在一对一的 user 和 profile 之间的关系。因此，你只需在 profile 表中添加一个 user_id 列，并将其定义为指向 user 表的外键。

然后，使用简单的 JOIN 就可以同时查询两个表：

SELECT u.*, p.*
FROM user u
JOIN profile p ON u.user_id = p.user_id

- Guillaume Poussel

---

我对Stack Overflow上的评论感到困惑。我创建了一个名为profiles的新表，其中包含：profiles_id、user_id、first_name...主键（profiles_id）和外键（user_id），它引用了用户表。现在，如果我要向表单中添加数据，那么我应该何时运行该查询？或者说，它在做什么？它是在设置表之间的某种关系吗？抱歉我这么困惑。 - mihai121

这确实是在创建表之间的关联，但插入和检索数据时需要考虑到此架构。您应该首先 INSERT INTO user (...) VALUES (...)，然后检索最后插入的id，并使用对用户的引用INSERT INTO profile(user_id, ...) VALUES ($last_id, ...)。 - Guillaume Poussel

好的，我已经做到了，但是我发现只有在插入之后才能请求最后插入的id。但是我有一个文件用于插入用户，另一个文件用于插入配置文件。我该怎么办才能检索last_id，以便我可以在插入到profiles文件时使用它呢？（我实际上并不使用PDO，所以我使用mysql_insert_id）。 - mihai121

出现错误：无法添加或更新子行：外键约束失败（projectdb.profiles，CONSTRAINT tb_fk FOREIGN KEY (user_id) REFERENCES user (user_id)）。 - mihai121

Answer 3

在User表中添加一个新的字段ProfileId，并将其设置为外键（FK）。每次创建用户时，您都需要将其分配给一个配置文件（这将是来自配置文件表的ProfileId PK）。 如果您还想查看用户的配置文件信息，则需要执行联接操作。

Select username, first_name,second_name
From user u, profile p
Where u.profileId = p.profileId 

这个

user_profiles
----------
user_id
profiles_id

在多对多的关系中使用。例如，您想将某些特权分配给管理员，但这些特权也可以分配给更多的管理员。那么，您必须创建第三个表来解决此问题。以下是一个示例，但您不需要执行此操作。

- Unix von Bash

Answer 4

你可以在个人资料表中添加一个user_id字段，然后在user_id上JOIN这些表。

SELECT user.user_username, ..., profiles.first_name, ...
FROM user
INNER JOIN profiles
ON user.user_id = profiles.user_id

这将获取数据，结合满足JOIN条件的那些行中的信息（即user.user_id = profiles.user_id）。

- xsc

Answer 5

有多个表是一个好主意，这是真的。我不确定您所说的访问时间是什么意思，但还有其他优点。 - 您的用户数据库包含密码等是“神圣的”，您永远不会更改其结构，并将对其的权限（读取、写入）限制在最严格的最小值。 - 然后，您可以拥有几个“卫星”表，例如配置文件、私人消息等，它们更加灵活、不太敏感，而且您可以随时更改。

关于您的问题本身，没有必要使用具有关系的单独表格。实际上，这是一个非常糟糕的想法，会使您的查询变得复杂，并且没有任何优势。相反，在您的配置文件数据库中，您将拥有一个列，该列将引用用户ID。

users
--------
id
user_name
email
password


users_profiles
---------
id
user_id
favourite_animal

- Joan-Diego Rodriguez

Answer 6

数据表 user

user_id |user_username |user_password |user_join_date |profile_id

数据表 profile

profile_id |first name |last name |email |age |gender

通过用户ID选择用户时：

SELECT u.*, p.* FROM user AS u INNER JOIN  `profile` AS p ON u.profile_id = p.profile_id WHERE u.user_id = 1 

但是一个用户应该只有一个性别，一个年龄，一个名字和姓氏。也许电子邮件地址可能有很多。我建议您不需要连接具有1对1关系的表。相反，合并这些表。

- zkanoca