What is the most efficient way to model the relati

Question

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.

Answer 1

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:

• How can I relate a primary key field to multiple tables?

• Structuring database relationships for tracking different variations of app settings

• How do I get around this relational database design smell?

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