数据库设计：简化多对多
假设我有以下表格：
- person (person_id, name)
- ethnicity (ethnicity_id, name)
- person_ethnicity (person_id,ethnicity_id)
这将允许我定义一个 person 具有 0 个或多个 ethnicity< /code> 和 ethnicity 通过 person_ethnicity 表拥有 0 个或更多 person。
现在，假设我有很多“种族”类型的表，我必须与 person 表建立相同的多对多关系。我的桌子数量将会快速增长。
使用这样的表是个好主意吗：
- foo (person_id, other_table_name, other_table_pk)
示例：
=================================================
| person_id | other_table_name | other_table_pk |
=================================================
| 1 | ethnicity | 1 |
-------------------------------------------------
我通过这种方式失去了引用完整性，但我认为会使建模变得更容易。这种方法是一个好主意还是一个可怕的主意？
（另外，我上面描述的方法有合适的名称吗？）
Say I have the following tables:
- person (person_id, name)
- ethnicity (ethnicity_id, name)
- person_ethnicity (person_id, ethnicity_id)
This would allow me to define a person to have 0 or more ethnicity AND an ethnicity to have 0 or more person through the person_ethnicity table.
Now, let's say I have a LOT of these "ethnicity" type tables where I have to make the same many to many relationship with the person table. The number of my tables are going to grow pretty fast.
Is it a good idea to have a table like this instead:
- foo (person_id, other_table_name, other_table_pk)
An example:
=================================================
| person_id | other_table_name | other_table_pk |
=================================================
| 1 | ethnicity | 1 |
-------------------------------------------------
I lose referencial integrity this way, but would make modeling much easier I think. Is this approach a good idea or a horrible, horrible idea?
(Also, is there a proper name for the approach I described above?)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我看没有必要。很多表并不麻烦，但这样做会打破很多“规则”。如果需要的话，就使用多对多。
用这种方式你应该可以做各种棘手的事情。此外，您无法使用外键（约束）执行任何操作，以及大量其他问题。为了什么？ “少桌子”。我没有看到任何优势：D
我的建议只是不要：D
I see no need. A lot of tables is no trouble, and you're breaking a lot of 'rules' by doing it like this. Just go with the many-to-many, if you need them.
Using it this way you should do all sorts of tricky things. Also, you can't do anything with foreign keys (constraints), and tons of other problems. And for what? "less tables". I don't see any advantage in that :D
Just don't would be my advice :D
既然你是从理论角度问这个问题，我就从同样的角度给你一个答案。
您的情况与标签系统非常相似。幸运的是，MySQL 社区提供了一篇关于 TagSchema via Forge 的优秀 wiki 文章。
此外，您还应该考虑搜索类似的问题，因为已经询问了并且询问和询问。其中一些实际上提供了对此事的有趣见解。特别是什么标签架构最高效/有效？ 以及制作包含
标签集的集合标签的响应。Since you're asking the question from a theoretical point of view, I'll give you an answer in the same regard.
Your situation is very similar to a tag system. And lucky for you the MySQL Community offers an excellent wiki article about TagSchema via Forge.
Also you should consider searching SO for similar questions, since it's been asked and asked and asked. Some of them actually provide interesting insight into the matter. Especially What tag schema(s) are the most efficient/effective? and the response on making
Collection Tagsthat holdTag Sets.如果您的模型中有很多多对多关系，例如您的种族示例，那么您别无选择，只能按照关系习惯所需的方式正确建模它们。
我不认为你的设计是个好主意。 Java 中没有绑定表名和列名；我不知道其他语言。
拿把铲子；开始工作吧。使用关系惯用法对存在的问题进行建模或寻找其他东西。对于您的情况来说，也许像对象或图形数据库这样的 NoSQL 解决方案是更好的主意。
If you have lots of many-to-many relationships in your model, like your ethnicity example, you have no choice but to model them properly the way the relational idiom requires.
I don't think your design is a good idea. Binding to table and column names isn't done in Java; I don't know about other languages.
Get a shovel; get to work. Model your problem as it exists using the relational idiom or find something else. Maybe a NoSQL solution like an object or graph database is a better idea in your case.
你的解决方案很快就会炸毁你的“foo”表。
假设您有 1000 人。那么每个人至少有 2 个种族。
那么你有一个国籍表，每个人也至少有 1.5 个国籍。
然后是性别，其中每个人至少有 1 个性别，总计已达 4500 个条目。
现在假设您的人数表增长到大约 50000 人。
这将在您的“foo”表中创建 225000 个条目。
现在你的表已经填满了，你可以使用 foo 和仅种族来进行查询，以获取所有人的种族，并且你的服务器将工作非常长时间，因为你将一个 50000 个表与一个 225000 个表连接起来，最后连接一个小桌子。
所以我希望我清楚地说明了为什么进行这样的布局不是一个好主意
Your solution would pretty fast blow up your "foo" table.
imagine you have 1000 persons. then every person has a minimum of 2 ethnicities.
then you have a nationality table and each person also has at least 1.5 nationalities.
then a gender where every person has a minimum of 1 gender what already sums up to 4500 entries.
now lets say your persons table grows to about 50000 persons.
this will make already 225000 entries in your "foo" table.
now that you have your tables filled up you make a query joining persons with foo and only ethnicity to get the ethnicities of all persons and your server will work verry verry long because you join a 50000 table with a 225000 table and in the end join a small table.
so i hope i made it clear why it is no good idea to make such a layout