在这些实体之间建立关系的最有效方法是什么？ [英] What is the most efficient way to model the relationship between these entities?
问题描述
我有一个包含如下实体的数据库：
1。用户实体
2.事件实体（音乐演唱会等）
3.票务实体
3.通知实体
- 通知实体与票证
和事件都有直接关系（1：N [票证/事件：通知]）。 - 通知将存储在数据库中的
，用户可以通过
的通知标签访问它们。 - 可以向用户通知与其机票相关的
通知（例如我们刚刚将机票发送给您！）或有关
某些事件（例如事件xy即将来临！请保留您的位置！）。
我一直在考虑三种可能的解决方案：
a）通知实体具有以下结构：
id串行主键
。
。
ticketId整数引用tickets（id），
eventId整数参考events（id））
userId整数REFERENCES users（id）//这在所有三个解决方案中都存在;
通过这种方式，Notification实体同时拥有两个外键，但一次只填充其中一个（无论是eventId还是ticketId），另一个永远永远为空。
b）通知实体只有与通知本身相关的列，不包含任何外部键（userId除外）。
该关系被提取到具有此结构的另外两个关系映射表中（对于Notification-Ticket关系，对于Notification-Event同样适用，但外键引用除外）事件）：
id串行主键，
notificationId整数引用notifications（id），
ticketId整数参考票证（id））；
通过这种方式，我们创建了类似接口的内容，并且不让Notification实体知道有关关系（它仅具有与通知本身和userId相关的属性），并且我们还有两个映射关系的表。
c）将Notification实体分为两个不同的实体实体
（TicketNotification，EventNotification），每个实体具有相同的属性，但外键列不同。
-TicketNotification-外键引用ticketId
-EventNotification-外键引用eventId
这样，我们有两个具有相同属性的表就在同一列中，这对我来说似乎不是很干。
我将非常感谢您提供的任何帮助和可能的解决方案，我可能会完全不赞成，并会从一个糟糕的角度来看待它。谢谢。
您不知道的是这个。您要声明的谓词是：
- 每个通知都与{事件|票证}
这需要互斥子类型群集。绝对，我们不想要可空外键，其后果是可怕的。这是正确的解决方案。
参考
请检查以下答案以从概念上理解问题，并且解决方案：
I have a database with entities as follows:
1. User entity
2. Event entity (musical concert etc.)
3. Ticket entity
3. Notification entity
- The notification entity has a direct relationship with both Ticket and Event (1:N [Ticket/Event : Notification]).
- Notifications will be stored in a database and user will have access to them via Notification tab.
- User can be notified with a notification related to his ticket(e.g. "We just sent your ticket to you!") or regarding some event (e.g. "Event xy is coming soon! Reserve your spot!").
I have been thinking about three possible solutions:
a) Notification entity has this structure:
id serial PRIMARY KEY,
.
.
ticketId integer REFERENCES tickets(id),
eventId integer REFERENCES events(id))
userId integer REFERENCES users(id) // this is present in all three solutions;
This way, the Notification entity holds both foreign keys, but only one of them is filled at a time (either eventId or ticketId) the other one stays forever empty.
b) Notification entity has only columns related to a notification itself, it does not hold any foreign keys (except userId).
The relationship is extracted to another two relationship mapping tables with this structure (for Notification - Ticket relationship, the same applies to Notification - Event, excepts the foreign key references event):
id serial PRIMARY KEY,
notificationId integer REFERENCES notifications(id),
ticketId integer REFERENCES tickets(id));
This way, we create something like an interface and don't let the Notification entity know anything about the relationships (it has only properties related to the notification itself and userId) and we have two additional tables mapping the relationship.
c) Separate Notification entity into two different entities
(TicketNotification, EventNotification), each of them has the same properties but varies in foreign keys column.
- TicketNotification - foreign key references ticketId
- EventNotification - foreign key references eventId
This way, we have two tables with the same properties varying just in one column, which does not seem very DRY to me.
I would be thankful for any kind of help and possible solutions, I may be completely off and looking at it from a bad perspective. Thanks.
What you do not realise is this. The Predicate you are declaring is:
- each Notification is related to one of { Event | Ticket }
That requires an Exclusive Subtype cluster. Definitely, we do not want Nullable Foreign Keys, the consequences are horrendous. This is the proper solution.
Reference
Please inspect these answers for a conceptual understanding of the problem, and the solution:
Relational Data Model
Note • Notation
All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993.
My IDEF1X Introduction is essential reading for beginners.
Refer to Subtype for full details re the understanding and implementation of Subtype
这篇关于在这些实体之间建立关系的最有效方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
