What is the most efficient way to model the relationship between these entities?

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.

Rossy

Can a notification relate to both a ticket and an event, or only one or the other? If it's the latter, you might want to add in a "notification type" either as an attribute of Notification or as a related NotificationType table and then use option a) as the data model since you would then be easily able to filter for only event or ticket notifications. If you might have more entities than just ticket or event in future that relate to notifications you might want to model that as a NotificationRelationship table instead, that has a "type" and a foreign key to the associated entity. – Sandysandye

I now realized that this wasn't the best example I could provide because here both entities Event and Ticket are somehow related one to each other(you buy a ticket for an event). It was just for simplicity, but in my real case, those entities are not related at all and so please consider that it is "one or the other" case scenario. Thanks for your answer. – Rossy

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

Lapp

Thanks a lot for your extensive answer. It looks great, so the solution is basically the solution described in b) except I have to divide the table which holds the relationships to separate tables and this way I won't have empty foreign keys. I am going to apply this principle to other parts of my database design as well (e.g. reviews etc.) so thank you a lot once again, It has been on my mind for a longer period of time and now I can finally proceed and move in the right direction. Thanks. – Rossy

@krstf. Yes. When done properly, the database is a logic map. Whereas you were working from the bottom, up ... I explained from the top, down. Make sure you read the Subtype. – Lapp

@Lapp I'm a junior DB-developer, and wonder whats so bad about nullable foreign keys. – Portative