如何设计数据库架构以通过许多其他表链接两个表 [英] How to design the database schema to link two tables via lots of other tables

问题描述

尽管我使用的是Rails，但这个问题更多地与数据库设计有关.我的数据库中有几个实体，其架构有点像这样: http://fishwebby.posterous.com/40423840

如果我想得到一个人的名单并按姓氏排序，那没问题.但是，如果要获取按姓氏排序的特定组中的人员列表，则必须使用SQL语句，该语句包含四个表之间的多个联接，如下所示:

SELECT group_enrolment.*, person.*
FROM person INNER JOIN member ON person.id = member.person_id
INNER JOIN enrolment ON member.id = enrolment.member_id
INNER JOIN group_enrolment ON enrolment.id = group_enrolment.enrolment_id
WHERE group_enrolment.id = 123
ORDER BY person.surname;

尽管这可行，但让我感到效率低下，并且随着我的模式的发展，这些查询可能会变得越来越复杂.

另一种选择是通过将person_id包括在其他表中，将人员表与查询中的所有其他表连接，例如，这将是一个单一的连接

SELECT group_enrolment.*, person.*
FROM person INNER JOIN group_enrolment ON group_enrolment.person_id
WHERE group_enrolment.id = 123
ORDER BY person.surname;

但这意味着在我的模式中，人员表已联接到许多其他表.除了复杂的架构图之外，没有人看到这有什么缺点吗?

对此，我将不胜感激-无论是我现在正在做的事情(多表联接)还是第二种解决方案，或者另一种我没有想到的解决方案都是最好的选择. /p>

非常感谢

解决方案

连接就是数据库的工作.话虽如此，您可能会考虑在模型中传播自然键，这将使您可以跳过联接中的某些表.看看 此示例 .

编辑

我并不是说这将与您的模型匹配(问题)，但只是出于娱乐目的，尝试对类似的查询进行类似的查询:

Although I'm using Rails, this question is more about database design. I have several entities in my database, with the schema a bit like this: http://fishwebby.posterous.com/40423840

If I want to get a list of people and order it by surname, that's no problem. However, if I want to get a list of people, ordered by surname, enrolled in a particular group, I have to use an SQL statement that includes several joins across four tables, something like this:

SELECT group_enrolment.*, person.*
FROM person INNER JOIN member ON person.id = member.person_id
INNER JOIN enrolment ON member.id = enrolment.member_id
INNER JOIN group_enrolment ON enrolment.id = group_enrolment.enrolment_id
WHERE group_enrolment.id = 123
ORDER BY person.surname;

Although this works, it strikes me as a bit inefficient, and potentially as my schema grows, these queries could get more and more complicated.

Another option could be to join the person table to all the other tables in the query by including person_id in the other tables, then it would just be one single join, for example

SELECT group_enrolment.*, person.*
FROM person INNER JOIN group_enrolment ON group_enrolment.person_id
WHERE group_enrolment.id = 123
ORDER BY person.surname;

But this would mean that in my schema, the person table is joined to a lot of other tables. Aside from a complicated schema diagram, does anyone see any disadvantages to this?

I'd be very grateful for any comments on this - whether what I'm doing now (the many table join) or the second solution or another one that hasn't occurred to me is the best way to go.

Many thanks in advance

解决方案

Well, joins are what databases do. Having said that, you may consider propagating natural keys in your model, which would then allow you to skip over some tables in joins. Take a look at this example.

EDIT

I'm not saying that this will match your model (problem), but just for fun try similar queries on something like this:

这篇关于如何设计数据库架构以通过许多其他表链接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！