用户反馈系统的正确数据库模型(一个有趣的案例)

2021-12-26 00:00:00 database foreign-key-relationship mysql relational-database database-design

我正在使用 PHP 和 Yii 框架开发应用程序.我一直在考虑最适合给定功能的数据库结构，这就是我想出的.然而，我并不是 100% 肯定应该这样做，所以我决定询问社区.

应用说明:

注册用户可以参加活动.每个事件都可以有一个无限数量的用户，称为活动的参与者").

活动结束后，每个参与者都可以留下关于同一活动的其他每个参与者的反馈.

数据库结构:

由于每个事件都可以有无限数量的用户并且用户可以参与无限数量的事件，因此我创建了一个表参与者"，它解决了多对多关系.

其他表格一目了然.

这里是最重要的事情:

一个事件的每个参与者可以拥有的最大反馈数等于除给定参与者之外的同一事件的参与者数量(例如，如果事件有 5 个参与者，给定参与者可以收到参与者的 4 个反馈)同一事件).

让我强调一下，只有同一事件的参与者才能留下关于给定参与者的反馈(并且只有一个).

以下是我为确保数据库完整性而采取的步骤:

  1. 我在参与者"表中创建了id"列，以便为参与特定活动的每个用户提供一个唯一 ID.这个 ID 是复合的(user_id 和 practice_id 连接在一起).因此，参与事件 14 的用户 23 的参与者 ID 将是 14-23.

你可能会问为什么我决定用这个 ID 创建一个单独的列，而不是像这样简单地定义主键:

PRIMARY KEY (user_id, event_id)

继续阅读.

活动结束后，每个参与者都可以留下对其他人的反馈.现在，这个参与者ID可以被反馈表中的外键sender_id"和recipient_id"引用.

再进一步，反馈表的主键也会由sender_id"和recipient_id"组合而成，所以如果用户23想给用户45(都参加了事件71)的反馈)，反馈的主键是:71-45-71-23.

这种方法使我们能够在数据库级别确保没有参与者对同一参与者留下两次反馈，并且用户不能两次参与同一事件.

问题:

  • 这种方法是否有权存在?
  • 优点和优点是什么?其他更好的方法来处理这个功能?
  • 我可以根据其他列的值生成主键吗自动插入记录?

解决方案

这是一个糟糕的设计.只需创建一个 2 列主键和 2 列外键即可.这是一种称为密钥中的编码信息"的基本反模式，(因此)称为智能"、智能"或连接"密钥.好键是哑"键.

例如::

<块引用>

尽管现在很容易实现智能钥匙，但很难建议您自己创建一个不是自然键的键，因为他们往往最终会遇到麻烦，无论他们优点，因为它使数据库更难重构，强加一个命令是难以更改并且可能不是您的查询的最佳选择，需要如果智能密钥包含非数字字符，则进行字符串比较，并且在帮助基于范围的聚合.它还违反了基本的关系准则，即每列都应该存储原子值

智能钥匙也往往会超出其原始编码限制

此外，没有需要这样做.

许多 DBMS 允许计算列"，其值是从其他列自动计算出来的.要使一个主键或外键，您通常需要持久化"它，即像普通列一样占用内存，而不是像视图一样在需要时进行计算.MySQL 没有这些，但是 5.7.5 有它们被称为生成的列"的一些功能，可以存储".但不要为 PK 或 FK 这样做！

实际的设计问题是处理数据库/SQL 子类型/层次结构/继承/多态性.

I am developing an application using PHP and Yii Framework. I've been thinking about the most suitable database structure for the given functionality and here's what I've come up with. Yet I'm not 100% positive that's how it should be done so I've decided to ask the community.

App Description:

Registered users may participate in an event. Every event can have an unlimited number of users, called "participants of the event").

Once the event is over, every participant can leave a feedback about every other participant of the same event.

Database structure:

Since every event can have an unlimited number of users and users can participate in an unlimited number of events, I've created a table "Participant", which resolves the many-to-many relation.

Other tables are self-explanatory.

And here's the most important thing:

Every participant of an event can have the maximum number of feedbacks which equals the number of participants of the same event excluding the given participant (Example, if there are 5 participants of the event, the given participant can receive 4 feedbacks from participants of the same event).

Let me emphasize, only participants of the same event can leave a feedback (and only one) about the given participant.

Below are the steps I took to ensure the integrity of the database:

  1. I've created the "id" column in the "Participant" table to give a unique ID to every user who participates in a certain event. This ID is composite (user_id and practice_id concatenated together). So, the participant id of the user 23 who participated in event 14 would be 14-23.

You may ask why I decided to create a separate column with this ID instead of simply defining the primary key like this:

PRIMARY KEY (user_id, event_id)

Read on.

When the event is over, every participant can leave a feedback about the others. Now, this participant ID can be references by the foreign keys "sender_id" and "recipient_id" in the feedback table.

Further on, the primary key of the feedback table will also be formed by combining "the sender_id" and the "recipient_id", so if the user 23 wants to leave a feedback about the user 45 (both participated in the event 71), the primary key for the feedback would be: 71-45-71-23.

This approach allows us to make sure on the database level that no participant leaves a feedback about the same participant twice and that a user can't participate in the same event twice.

Questions:

  • Does this approach has the right to exist?
  • What are the pros and other, better way to approach this functionality?
  • Can I generate the primary keys based on the values of the other columns automatically on record insertion?

解决方案

This is a bad design. Just make a 2-column primary key, and 2-column foreign keys to it. This is a fundamental anti-pattern called "encoding information in keys" which (thereby) are called "smart", "intelligent" or "concatenated" keys. A good key is a "dumb" key.

Eg::

Despite it now being easy to implement a Smart Key, it is hard to recommend that you create one of your own that isn't a natural key, because they tend to eventually run into trouble, whatever their advantages, because it makes the databases harder to refactor, imposes an order which is difficult to change and may not be optimal for your queries, requires a string comparison if the Smart Key includes non-numeric characters, and is less effective than a composite key in helping range-based aggregations. It also violates the basic relational guideline that every column should store atomic values

Smart Keys also tend to outgrow their original coding constraints

Besides, there is no need to do this.

Many DBMSes allow "computed columns" whose values are automatically calculated from other columns. To make one a primary key or foreign key you would usually need it "persisted", ie have take up memory like a normal column vs just being calculated when needed like a view. MySQL does not have these, but 5.7.5 has some functionality where they are called "generated columns", which can be "stored". But don't do this for PKs or FKs!

The actual design issue is handling database/SQL subtypes/hierarchies/inheritance/polymorphism.

相关文章