存储“冗余"消息.避免加入外键 [英] Storing "redundant" foreign keys to avoid joins

问题描述

我正在为一个Web应用程序项目设计一个数据库，得出的结论是，我可能有很少的查询，这些查询需要很多联接表才能进行一次检查.

I'm designing a database for a web application project and I came to the conclusion I may have few queries that will require a lot of joined tables to just make one check.

我想知道将外键存储在减少这些查询所需的联接数量的途中有多糟?

I'm wondering how bad is it to store the foreign key somewhere on the way to decrease number of joins required for these queries?

给你一个我现在所拥有的例子:服务=>预订=>交易=>钱包=> BonusOffer

To give you an example of what I have at this moment: Service => Booking => Transaction => Wallet => BonusOffer

我需要检查该服务是否已经用与奖金相关联的钱包购买了.将BonusOffer ID存储为Transaction的外键是否明智?

I need to check whether the service has been bought with wallet associated with a bonus. Would it be wise to store BonusOffer id as foreign key of Transaction?

您可能会问为什么使用事务处理-这是因为大多数查询将通过"事务处理，而事务处理将位于中间位置.

You could ask why Transaction - it's because most of these queries will "go through" Transaction and Transaction will be somewhere in the middle.

推荐答案

联接是关系型DBMS的工作方式.了解并使用规范化.

Joins are how relational DBMSs work. Learn about and use normalization.

我需要检查该服务是否已与带有奖励的钱包一起购买.

I need to check whether the service has been bought with wallet associated with a bonus.

如果对于每个服务都是如此，那么您的数据库将受到约束.这是(从Service_has_transaction联接选择服务中选择服务)的子集.

If this is true for every service then your database is subject to a constraint. It is that (select service from Service_has_transaction join Transaction_has_wallet) is a subset of (select service from Service_has_transaction join Transaction_has_wallet join Wallet_has_bonus).

大多数SQL DBMS不允许您声明性地表示该约束，也不知道如何优化实施.但是，我们可以使用一个SQL习惯用语来表达&以声明方式执行.(根据您的表定义进行猜测:)首先在 Transaction_has_wallet 中添加 bonus 列，然后将 Transaction_has_wallet(钱包，赠金)中的外键添加到 Wallet_has_bonus .然后添加钱包 Service_has_transaction 的奖励列和 Service_has_transaction(交易，钱包，奖励)到 Transaction 的外键.这将添加冗余列，但由于外键约束可防止冗余值出错，因此将数据库限制为有效状态.(希望这是学习通过触发器表达任意约束的动机示例.)

Most SQL DBMSs don't let you express that constraint declaratively and don't know how to optimize enforcing it. However there is an SQL idiom that we can use to expresss & enforce it declaratively. (Guessing at your table definitions:) First add a bonus column to Transaction_has_wallet and a foreign key from Transaction_has_wallet (wallet, bonus) to Wallet_has_bonus. Then add wallet & bonus columns to Service_has_transaction and a foreign key from Service_has_transaction (transaction, wallet, bonus) to Transaction. This adds redundant columns but nevertheless limits the database to valid states because the foreign key constraints prevent the redundant values from being wrong. (Hopefully this is a motivatig example for learning about expressing arbitrary constraints via triggers.)

这篇关于存储“冗余"消息.避免加入外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！