SQL - Best practice for One-to-Many with Multiple "One" Tables

Question

My scenario is difficult too explain in just a short title, so here's an example:

I have multiple tables "birthday_event", "meeting_event", "dinner_event" and they have multiple children in a table called "attendees". Essentially, I have 3 distinct One-to-Many relationships: "birthday_event" has many "attendees", "meeting_event" has many "attendees", and so on.

Each of the "event" tables have their own specific attributes. Normally, I would have a foreign key "event_id" in "attendees" so the relationship is robust, but there are many different event tables that can fulfill the "event_id" attribute, so it won't be a strict foreign key, if that makes sense.

My understanding is that when a foreign key is established, it is strongly linked to an attribute in a single table, not multiple tables like the 3 event tables in the example. It is also my understanding that foreign keys should not ever be null so as to ensure robustness of data. So, what is the best practice for establishing a robust relationship between my many events and many attendees?

My possible solutions:

Use "event_id" as a normal attribute in the "attendees" table so that all three events can use "attendees". The drawback is that this is only a pseudo-foreign key.
Create 3 different "attendee" tables for each event, so there would be "birthday_attendee", "meeting_attendee", and "dinner_attendee" tables. The drawback is more tables.
Using a junction table like "event_attendee" doesn't work because then this table will also have a problem with linking a single foreign key "event_id" to multiple event tables.

Which of these or other solutions is ideal for making the database design understandable, scalable, and high-performance?

By the way, I am planning to conduct model operations on these database tables using a Django backend, if that has any affect on the answer.

This is a faq, google re database/sql subtyping/inheritance/polymorphism. The multiple FKs design is an anti-pattern. You would find this if you actually tried to google, as you always should, many clear, concise & specific versions/phrasings of your question/problem/goal & read many answers. — philipxy, Mar 07 '18 at 20:15
Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Mar 07 '18 at 20:18
That duplicate relates to MSSQL and does not take into consideration the functionality available in MSSQL not available in PostgreSQL and vice versa — e_i_pi, Mar 07 '18 at 20:29

score 2 · Accepted Answer · answered Mar 07 '18 at 20:14

2

Postgres has a solution for you, with table inheritance. It is well described in the documentation.

Basically, you can have an events table and then have three different types of events. You can have a foreign key reference to either the specific events or to the parent events table.

For instance, you can have a generic eventAttendees table. This would have a foreign key to events. But the reference could be to any of the specific event types. The specific event types would then have the common columns from event (perhaps only eventId) and only the columns for that specific type.

answered Mar 07 '18 at 20:14

Gordon Linoff

1,242,037
58
646
786

greatest invent ever after row_number(). – Juan Carlos Oropeza Mar 07 '18 at 20:17
This makes a lot of sense. I guess the pattern I was looking for was inheritance. I'll attempt to solve this using Django model inheritance and postgresql. Thank you. – Boberoni Mar 07 '18 at 20:55

score 1 · Answer 2 · answered Mar 07 '18 at 20:26

It sounds like you may have a slight design problem, so I'll address that first before I go on to your question in the current context.

Can an event be more than one of the following: birthday, meeting, dinner? If not, then the event table should have en event_type column which determines which event type it is. The event table should then hold all columns that are common across all events. For extra columns, you can have the birthday_event, meeting_event, and dinner_event tables, that 0/1-1 with the event table.

So what is this 0/1-1? A one-to-one relationship where one side is nullable. So yes, it is fine for a foreign key to be nullable. A nullable foreign key just means that there may or may not be a relationship, but that relationship must be against the table that is foreign key constrained to.

Since you have tagged this as PostgreSQL, I will offer another alternative - one event table with the event_type column, and an event_details column of type jsonb. This is a relatively new PostgreSQL type that allows you to store JSON data in a field, and then interrogate that column like it's a deeply nested array of columns. (See PostgreSQL documentation on the json/jsonb types)

I would say that no matter if you have a central event table with all the common information, or if you farm the information out to the three satellite tables, you still want to go with an event_attendees table to store the attendees. Attendees relate to an event, they are not concerned with an event type.

SQL - Best practice for One-to-Many with Multiple "One" Tables

2 Answers2