Search code examples
databasedatabase-designforeign-key-relationshipdatabase-normalizationdatabase-table

One table column for many fk tables?

What is the best solution/practice for situation like this? I have a table, that can reference to multiple tables (objects)?

Here is an example for a table UserCalendar. It's a table where user saves his event, but also system inserts in this table from behind. User executes some services, which have deadlines and those are inserted in this table also. Problem is that there is no such a table as UserEvent table. User should save all his events in this calendar as description. I should make this simple as possible.

I can design this two way.

1)

UserCalendar
UserCalendarId | UserId | Description | ObjectType | ObjectId

Using this option I would not have to FK this tables. I could only change ObjectType (Notification,Service,Calendar) and use id of that Table as ObjectId. In case of Event there will be no such a Table and this would be null field. We an call this pseudo FK column.

2) Or I could use as says in theory with multiple tables for each FK.

UserCalendar
UserCalendarId | UserId | Description

UserEvent
UserCalendarId |EventId

UserServices
UserCalendarId|ServiceId

UserNotifications
UserCalendarId |NotificationId
...

This foreign relationships tables can be a n number, for each system event or any other custom event that belongs to certain type

First solution is a fast implementation.

Solution

  • I'd prefer a 3rd solution as a mix of your two designs:

    UserCalendar
    UserCalendarId | UserId | Description

    UserCalendarAttributes
    UserCalendarId | ObjectType | ObjectId

    This way you are free to add as many additional references to your calendar entry as you wish (e. g. event and notification) and you don't have a tight coupling between the UserCalendar table and any other tables.

    It also depends a little bit on how often you have to access that additional data (events, service, etc.) if it makes sense to put the FK in your main UserCalendar table.

    I suggest going for flexibility more than for performance, though you will slightly increase the complexity of your schema. It's more likely that feature requirements will change than you will be hit by performance problems.