关系型数据库设计（MySQL）
我正在为一个基于“人才”的网站启动一个新项目 - 例如：
- 模特
- 演员
- 歌手
- 舞者
- 音乐家
我建议这样做的方式是，每个人才都有自己的表，并包含一个 user_id 字段来映射记录给特定用户。
任何在该网站注册的用户都可以为这些人才中的一个或多个创建个人资料。人才可以有子人才，例如演员可以是电视演员或戏剧演员或配音演员。
例如，我有用户 A - 他是一名模特（T 台模特）和一名演员（电视演员、戏剧演员、配音演员）。
我的问题是：
我是否需要创建单独的表来存储该用户的子人才？
如何查找该用户的顶级人才？即用户表中是否应该有每个人才的ID字段？或者我应该在每个顶级人才表中执行查找以查看该 user_id 是否存在于其中？
还有什么我应该注意的吗？
I am starting a new Project for a website based on "Talents" - for example:
- Models
- Actors
- Singers
- Dancers
- Musicians
The way I propose to do this is that each of these talents will have its own table and include a user_id field to map the record to a specific user.
Any user who signs up on the website can create a profile for one or more of these talents. A talent can have sub-talents, for example an actor can be a tv actor or a theatre actor or a voiceover actor.
So for example I have User A - he is a Model (Catwalk Model) and an Actor (TV actor, Theatre actor, Voiceover actor).
My questions are:
Do I need to create separate tables to store sub-talents of this user?
How should I perform the lookups of the top-level talents for this user? I.e. in the user table should there be fields for the ID of each talent? Or should I perform a lookup in each top-level talent table to see if that user_id exists in there?
Anything else I should be aware of?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
在回答您的问题之前...我认为 user_id 不应该出现在人才表中...这里的主要思想是“对于 1 个人才，您有许多用户，对于一个用户，您有多个人才”..所以关系应该是 NxN，您需要一个中间表
，请参阅： 多对多现在
如果您想做一些动态的事情（添加或删除子人才），您可以使用递归关系。这是一个与其自身相关的表
，请参阅：递归关联
如果您以前使用过该模型，那么进行查询可能是一场噩梦，因为您的表 Talents 现在是一棵可以包含多个级别的树。您可能希望将自己限制在您想要的特定级别数人才表我想两个就足够了..这样你的查询会更容易
当使用递归关系时...外键应该允许空值，因为顶级人才不会有parent_id...
祝你好运！ :)
编辑： 好的..我已经创建了模型..为了更好地解释它
编辑 第二个模型（圣诞树形状=D）注意模型与模型之间的关系人才和演员人才是 1x1 的关系，有不同的方法可以做到这一点（相同评论上的链接)
查找用户是否有才能.. 在查询中连接三个表=)
希望这有帮助
before answering your questions... i think that user_id should not be in the Talents table... the main idea here is that "for 1 talent you have many users, and for one user you have multiple talent".. so the relation should be NxN, you'll need an intermediary table
see: many to many
now
if you want to do something dynamic (add or remove subtalents) you can use a recursive relationship. That is a table that is related to itself
see : recursive associations
if you're using the model before, it could be a nightmare to make queries, because your table Talents is now a TREE that can contain multiple levels.. you might want to restrict yourself to a certain number of levels that you want in your Talent's table i guess two is enough.. that way your queries will be easier
when using recursive relations... the foreign key should alow nulls because the top levels talents wont have a parent_id...
Good luck! :)
EDIT: ok.. i've created the model.. to explain it better
Edit Second model (in the shape of a Christmas tree =D ) Note that the relation between Model & Talent and Actor & Talent is a 1x1 relation, there are different ways to do that (the same link on the comments)
to find if user has talents.. join the three tables on the query =)
hope this helps
您应该有一张表，其中包含有关用户的所有信息（姓名、出生日期、有关用户的任何其他信息）。您应该有一张包含有关人才的所有信息的表（id、talentName、TopLevelTalentID（要存储“子”人才，请引用“父”人才））。您应该有第三个表来表示用户和人才之间的多对多关系：UserTalents，它存储 UserID 和 TalentID。
这里有一篇文章解释了如何达到第三个 NF：
http://www.deeptraining.com /litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
You should have one table that has everything about the user (name, dob, any other information about the user). You should have one table that has everything about talents (id, talentName, TopLevelTalentID (to store the "sub" talents put a reference to the "Parent" talent)). You should have a third table for the many to many relationship between users and talents: UserTalents which stores the UserID and the TalentID.
Here's an article that explains how to get to 3rd NF:
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
这是一个很好的问题，展示了面向对象思维和关系建模之间的一些差异和相似之处。
首先，关于创建表没有严格的规则，这取决于您尝试建模的问题空间（但是，每个表都有一个字段根本没有必要，并且构成了设计错误 - 主要是因为它是不灵活且难以查询）。
例如，在这种情况下完全可以接受的设计是拥有表格。
上面的设计将允许您拥有分层的 TalentType，并跟踪哪些名字具有哪些天赋，您将拥有一个可以从中获取所有名字的表（以避免注册重复项），您有一个表，您可以从中获取人才列表，并且可以轻松添加新的人才类型和/或子类型。
如果您确实需要在每种人才类型上存储一些特殊字段，您仍然可以将它们添加为引用通用人才表的表。
作为说明
请注意，这只是一个说明，在人才表中包含薪资而不是针对特定人才的表可能是明智的做法。
如果您最终得到了特定人才的表格，那么您可以将人才表视为特定人才或子人才继承属性的类。
This is a good question to show some of the differences and similarities between object oriented thinking and relational modelling.
First of all there are no strict rules regarding creating the tables, it depends on the problem space you are trying to model (however, having a field for each of the tables is not necessary at all and constitutes a design fault - mainly because it is inflexible and hard to query).
For example perfectly acceptable design in this case is to have tables
The above design would allow you to have hierarchical TalentTypes and also to keep track which names have which talents, you would have a single table from which you could get all names (to avoid registering duplicates), you have a single table from which you could get a list of talents and you can add new talent types and/or subtypes easily.
If you really need to store some special fileds on each of the talent types you can still add these as tables that reference general talents table.
As an illustration
Do notice that this is only an illustration, it might be sensible to have Salary in the Talents table and not to have tables for specific talents.
If you do end up with tables for specific talents in a sense you can look at Talents table as sort of a class from which a particular talent or sub-talent inherits properties.
好吧，抱歉，答案不正确。这是一种不同的方法。
在我看来，一个用户可以有多种职业（演员、模特、音乐家等）。通常我所做的就是首先思考对象，然后将其转换为表格。在 POO 中，您将有一个 User 类和 Actor、Model 等子类。每个子类还可以有 TvActor、VoiceOverActor 等子类...在 DB 中，您将为每个人才和子人才提供一张表，所有这些共享相同的主键（用户的 id），因此如果用户 4 是演员和模型，那么您将在演员表上有一个注册表，在模型表上有另一个注册表，两者的 id=4
如您所见，存储很容易......复杂的部分是检索信息。那是因为数据库没有继承的概念（我认为 mysql 有，但我还没有尝试过）..所以如果你现在想要用户 4 的子类，我会看到三个选项：
每个人才的多个 SELECT和你拥有的 subtalent 表，询问他们的 id 是否为 4。
SELECT * FROM Actor WHERE id=4;SELECT * FROM TvActor WHERE id=4;
进行一个大查询，在左连接上连接所有人才和次人才表
SELECT * from User LEFT JOIN Actor ON User.id=Actor.id LEFT JOIN TvActor ON User.id=TvActor.id LEFT JOIN... WHERE User.id=4;
创建一个与用户具有 NxN 关系的人才表，以存储用户拥有的每个人才和子人才的参考，因此您不必询问所有表。您必须对 Talents 表进行查询，以找出在第二个查询中需要询问哪些表。
这三个选项中的每一个都有其优缺点..也许还有另一个 =)
祝你好运
PS: 啊我找到了另一个选项这里 或者也许只是改进了第二个选项
ok sorry for the incorrect answer.. this is a different approach.
The way i see it, a user can have multiple occupations (Actor, Model, Musician, etc.) Usually what i do is think in objects first then translate it into tables. In P.O.O. you'd have a class User and subclasses Actor, Model, etc. each one of them could also have subclasses like TvActor, VoiceOverActor... in a DB you'd have a table for each talent and subtalent, all of them share the same primary key (the id of the user) so if the user 4 is and Actor and a Model, you would have one registry on the Actor's Table and another on the Model Table, both with id=4
As you can see, storing is easy.. the complicated part is to retrieve the info. That's because databases dont have the notion of inheritance (i think mysql has but i haven't tried it).. so if you want to now the subclases of the user 4, i see three options:
multiple SELECTs for each talent and subtalent table that you have, asking if their id is 4.
SELECT * FROM Actor WHERE id=4;SELECT * FROM TvActor WHERE id=4;
Make a big query joining all talent and subtalent table on a left join
SELECT * from User LEFT JOIN Actor ON User.id=Actor.id LEFT JOIN TvActor ON User.id=TvActor.id LEFT JOIN... WHERE User.id=4;
create a Talents table in a NxN relation with User to store a reference of each talent and subtalents that the User has, so you wont have to ask all of the tables. You'd have to make a query on the Talents table to find out what tables you'll need to ask on a second query.
Each one of these three options have their pros and cons.. maybe there's another one =)
Good Luck
PS: ahh i found another option here or maybe it's just the second option improved