一般来说，在数据库设计中，一张带有两个引用的表和一张表哪个更好？
假设有人在找工作，我有一份工作清单。 所以我有两张桌子： 人员和工作。 现在我有一个人的技能列表，以及工作要求的技能列表。
拥有一张这样的技能表更好：
CREATE TABLE skills_reference
(
id INT,
reference_id INT, -- can reference people(id) or job(id)
reference ENUM('person','job'),
skill FOREIGN KEY REFERENCE skills(id)
)
或者拥有两张表，一张用于 people_skills，一张用于 jobs_skills。 哪一个会带来更好的性能结果？
谢谢。
Let's say i have people who search for jobs, and i have a list of jobs.
So i have two tables:
people and jobs.
Now i have a list of skills of the person, and i have a list of skills for the job requirement.
What is better to have ONE skills table like this:
CREATE TABLE skills_reference
(
id INT,
reference_id INT, -- can reference people(id) or job(id)
reference ENUM('person','job'),
skill FOREIGN KEY REFERENCE skills(id)
)
OR to have TWO table, one for people_skills and one for jobs_skills.
Which one will give the better performance results?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
IMO，您应该创建两张表，一张用于
job_skill(job_id, Skill_id)，一张用于person_skills(person_id,skill_id)。不过，两者都指向相同的技能表。我应该指出，性能只是一个考虑因素，在很多很多情况下，您应该首先关注数据模型的逻辑合理设计，然后关注性能（如果它确实是一个问题）。
对于 RDBMS 工作，在许多情况下 (80%)，最简洁的设计对于性能也是最好的。
IMO, you should make two tables, one for
job_skill(job_id, skill_id)and one forperson_skills(person_id,skill_id). Both point to the same skills table though.I should point out that performance is just one consideration, and in many, many cases, you should first focus on logical sound design of th data model and then on performance (if it is a problem at all).
With RDBMS work, in many cases (80%), the cleanest design is also best for performance.
我想如果我设计这个，我就会有一个主技能表，其中包含所有定义的技能。然后我会有一个 JobSkills 表和 PeopleSkills 表。两者都会有一个对主技能表的 FK 引用。
I think if I were designing this, I would have a master Skills table that contains the universe of defined skills. I would then have a JobSkills table and PeopleSkills table. Both would have a FK reference to the master Skills table.
性能结果将取决于使用情况。人们会主要寻找人际技能、工作技能，还是技能与工作相匹配的人？您期望数据频繁更新吗？您的数据可接受的“新鲜度”是多少（即，更改应多快反映在搜索中）？您使用什么系统 Oracle、MSSQL、MySQL...？
适用于任何事物的通用性能规则很少。编程并不是那么无思想的。
Performance results will depend on usage. Will people primarily search for just peopleskills, or just job skills, or people whose skills match a job? Do you expect frequent updates of data? What's an acceptable "freshness" to your data (ie, how quick should a change be reflected in the search)? What system are you using Oracle, MSSQL, MySQL...?
There are very few universal rules for performance that apply to anything. Programming just isn't that thought-free.
就我个人而言，我会使用两个表：
people_skills和job_skills。我发现以这种方式思考并编写针对它们的连接更容易。问候
钾
PS
如果您的两个表变得很大，您始终可以将它们移动到单独的磁盘，以减少执行大量查询时的 io 争用。
Personally I would use two tables,
people_skillsandjob_skills. I find it easier to think about it that way and to write joins against them.Regards
K
PS
If your two tables ever get huge, you can always move them to separate disks to reduce io contention when doing heavy queries.