从长远来看，创建易于查询的SQL表的最佳方法是什么？

Question

我目前正在为一个客户做一个项目。他的需求发生了变化，所以我的数据库结构也必须改变。

我的客户经营一家旅行社，他每天都要登记很多人。他想要实现的是能够组织乘客。所以在管理界面中，你点击你想要分组的乘客，瞧。他们是一群人。

在每一组中，你可以分配额外的东西，例如午餐，饮料，租用设备，这样工作人员就可以给那个组制定价格。

下面是我需要的内容，我的问题是如何从这里构建SQL表。我目前正在阅读一种--很多--许多关系，外键等等。从长远来看，我希望能够查询数据库，而不存在任何问题，在我过去的经历中有很多问题：

Groups
gp_ID    customer_ID    leader    fare_Type    dependant_Fare_Child    dependant_Fare_Adult

Tour_Extras
TE_ID    TE_Name

Tour_Extra_Price
TE_ID    TE_Price    fare_ID

Fare_Type
fare_ID    fare_Name

Group_Extras
gp_ID    TE_ID    quantity    fare_ID

组包含该组中的所有乘客。只有一个领导和customer_ID将被用作参考另一个表，所有接触个人为该乘客。fare_Type将是一个int，它将引用Fare_Type表。dependant_Fare_Ch和成人也是整数。这将决定有多少儿童和儿童按成人价格分配给受抚养人。

Tour_Extras持有类型的临时组可以拥有。例如午餐，齿轮出租

Tour_Extra_Price持有特定额外的价格以及fare_ID的价格，这将决定收费的价格(儿童、成人、老年人)。

Fare_Type持有fare_Id和姓名(儿童、成人、老年人)

Group_Extras，这是最大的。它将引用组、tour_extra和可能的Fare_Type表。这将用于确定该特定组的总金额。例如：

gp_ID TE_ID quantity fare_ID
  1     1      3       1

对于id为1的组，他们选择了TE_ID 1(如午餐)和购买数量(3)，并将被收取fare_ID 1(例如儿童)费用。

这可能对某些人有帮助：

Tour_Extras可以有许多Tour_Extra_Price

Tour_Extra_Price可以有一个Fare_Type

Group_Extras可以有许多组，Tour_Extras和/或Fare_Type (不确定是直接与Fare_Type链接，还是通过Tour_Extras，然后是Tour_Extra_Price，然后是Fare_Type)

我还有其他问题，比如需要外键吗？是否最好在Group_Extras表上有主键，在其他表上有外键？

很抱歉，长时间的帖子，但我想用尽所有的选择，并做我的研究之前，潜入这个项目。

我要几个小时后才能回来。所以，请提问，我会在12小时内回答。

Answer 1

显然，我们彼此相隔了几个小时，所以在“请求评论”部分的问答往返需要花费太多的时间。因此，与其每天多问一次问题，我的想法是：

首先: customer_id实际上代表什么？您是否有客户的存储，所以您认识他们时，他们反复预订旅游？在我看来并不是这样(不管怎样，你会从中得到什么？)据我所见，在我看来，这更像是旅游预订。所以每次米勒先生预定一次旅行，他都会得到一个新的customer_id。但是如果是这样的话，customer_id不是已经代表他的全部预订了吗？您不需要为米勒夫人提供customer_id，因为她将被包括在预订中。

所以我看到的是预订。这包括一个人的姓名和联系方式(在我的例子中是Miller先生)，加上旅行者的数量。这将是：

桌预订

• booking_id
• tour_id
• 名字
• 地址
• 电话

表booking_passengers

• booking_id
• fare_type_id (成人/儿童/老年人)
• 数量(这类订票中有多少个)

数据应该始终在表中，而不是在表名或列名中。因此，您将不会有像dependant_Fare_Child和dependant_Fare_Adult这样的列，而是一个带有列fare_id的表booking_passengers，因此您可以根据ID进行连接。

因此，预订(或您所称的客户)已经代表了一个组。您是否需要更多关于共同乘客的详细信息，然后将booking_passengers替换为一张每名乘客保存一张记录的桌子：

表booking_passenger

• booking_id
• fare_type_id (成人/儿童/老年人)
• 名字
• 年龄

你的临时旅行的结构看起来很好。当我称您的客户或组为预订时，我的桌子将是：

表booking_extras

• booking_id
• tour_extra_id (午餐/装备/.)
• fare_type_id (成人/儿童/老年人)
• 数量

就旅游价格而言，我假设有这样一张桌子，顺便说一句：

表tour_price

• tour_id
• fare_type_id (成人/儿童/老年人)
• 价格

上表中的主键为斜体。你应该用外键。它们的存在是为了保证数据的一致性，例如，在没有预订的情况下不会有乘客。数据库用户总是认为在关系数据库中拥有外键是理所当然的，并在数据一致性和可用索引方面依赖外键的存在。因此，表booking_extras将有一个主键booking_id + tour_extra_id + fare_type_id。您将拥有通过booking_id预订表的外键、通过tour_extra_id预订的tour_extra密钥和通过fare_type_id预订的fare_type密钥。

希望这能有所帮助。如果我误解了什么，只要在评论中告诉我，我希望有时间相应地调整我的答案。

Answer 2

您的数据库结构通常适合您所描述的需求。我建议您添加一个" group“表，用于为组成员共享的公共属性，例如ID、必要时组的名称或时间戳。您也可以在这里直接设置领导者的ID，而不是在groups表的每一行中都有一个“领导者”标志。

除此之外，我要做一个更改；因为您使用的是MySQL，所以您可以选择为fareType而不是Fare_Type表使用ENUM字段(因为我假设这3个值不太可能改变--如果它们更改了，您可以轻松地添加更多的值)。这将简化您的查询，因为您必须少做一个联接，而且在调试时也会使您的数据库更具可读性。在学术上，您的解决方案是正确的，我只想改变这一点，使您的生活更容易一些(并使数据库更加可读性)。当然，如果用户希望自己编辑fareTypes，则必须将其保留为单独的表。

从技术上讲，您不需要任何外键--如果只添加主键并在更新/删除/插入逻辑中记住关系，应用程序就会正常工作。如果添加外键，它将在表上添加约束，从而不会破坏它们的完整性。例如，如果要删除Tour_Extra，首先必须确保没有其他表对其具有引用，否则就会出现约束错误。虽然情况有所改善，但我并不热衷于在MySQL中使用外键，过去存在很多问题。如果选择使用它们，则需要在任何情况下将表设置为InnoDB数据库引擎。

您的主键应该非常清楚：

• 集团-> gp_ID (auto_increment)
• -> gp_ID，customer_ID组
• Tour_Extras -> TE_ID (auto_increment)
• Tour_Extra_Price -> TE_ID，fareType (enum)
• Group_Extras -> gp_ID TE_ID，fareType (enum)

以及外键，如果您选择分配它们：

• 组-> gp_ID references Group.gp_ID customer_ID references customer.customer_ID
• Tour_Extra_Price -> TE_ID references Tour_Extra.TE_ID
• Group_Extras -> gp_ID references Group.gp_ID，(TE_ID，fareType)引用(Tour_Extra_Price.TE_ID，Tour_Extra_Price.fareType)