用户反馈系统的正确数据库模型（一个有趣的情况） [英] Proper database model for a user feedback system (an interesting case)

问题描述

我使用PHP和Yii Framework开发应用程序。我一直在考虑给定的功能最合适的数据库结构，这是我想出的。

应用程式说明： / p>

注册用户可以参加活动。每个活动可以有一个
无限数量的用户，称为活动参与者）。

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

数据库结构：

由于每个活动都可以有无限数量的用户并且用户可以参与无限数量的事件，我创建了一个表Participant，它解决了多对多关系。

其他表是self -explanatory。

这里是最重要的事情：

事件的每个参与者都可以有最大数量的反馈等于除了给定参与者之外的相同事件的参与者的数目（例如，如果事件的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。

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

li>这种方法是否有权存在？

• 什么是专业人员和
  其他，更好的方式来接近这个功能？


• 我可以根据记录插入时其他列
  的值自动生成主键吗？

解决方案

这是一个不好的设计。只需创建一个2列的主键和2列的外键即可。这是被称为密钥中的编码信息的基本反模式，其（因此）被称为智能，智能或级联密钥。一个好的关键是哑键。

例如：

尽管现在很容易实现一个智能钥匙，很难
推荐你创建一个自己的，不是一个自然的钥匙，
，因为他们往往最终遇到麻烦，无论他们
的优点，因为它使得数据库更难重构，强加
一个顺序是
难以更改，可能不是最适合您的查询，需要
a字符串比较如果智能键包括非数字字符
，并且在帮助基于范围的
聚合中不如复合键有效。它也违反了基本的关系准则，
每一列都应该存储原子值

智能键也倾向于超出原来的编码约束

此外，没有需要。

允许计算列，其值从其他列自动计算。要使一个主键或外键，你通常需要它持久化，即占用内存像一个普通的列，而只是在需要时计算视图。 MySQL没有这些，但 5.7.5 有一些功能，其中它们被称为生成列，其可以是存储的。但不要这样做PKs或FKs！

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!

这篇关于用户反馈系统的正确数据库模型（一个有趣的情况）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！