CODEWITHSUNDEEP
sqldatabasedatabase-design
Mirko
Mirko

Reputation: 38

Table with foreign key that can reference different tables

I'm trying to build a table for an inbox app that stores its messages within inbox table.

The structure of the table is as follows:

inbox_id|sender_id|receiver_id|subject|message

The columns sender_id and receiver_id are FOREIGN KEYS and can reference multiple tables.

There are currently 3 types of users within the database and they all can send messages to each other. A user of type UserType1 can send a message to UserType2 and vice versa, or UserType1 can send messages to UserType1. So receiver and sender can reference one of these 3 tables.

My solution to this problem is to build a inbox_user table containing columns for each user type and to have sender_id and inbox_user reference it.

My main concern is limited flexibility of the solution and wasteful usage of resources. I would, at all times, always have 2 empty columns per row. And that would become even worse if I introduced more user types.

Would this be considered a bad practice? What are some more flexible and intelligent designs?

Upvotes: 0

Views: 611

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

3 different types of users is a classic type/subtype situation. (or, if you prefer, class/subclass). There are several ways to design for a class/subclass situation. Two that look good to me are "Class Table Inheritance" and "Single Table Inheritance" as explained by Martin fowler. You can find a synopsis online. You can also visit tags by these names in here, read up on the info presented, and look at the tagged questions.

Single table inheritance suffers from a lot of NULLs for fields that are not applicable some of the time, as you pointed out in the Q. This may or may not be a problem for you, depending on your case.

Class table inheritance involves a little programming when a new entry is made in a subclass (subtype). It also involves more joining, but it isn't very expensive joining. Class table inheritance is frequently combined with a technique called shared primary key. In this technique, the subclass tables end up with a primary key that is a duplicate of the primary key in the superclass table. It's also a foreign key to the superclass table. This makes joining subclass data and superclass data simple, easy, and fast.

Shared primary key resolves the quandary you stated in your Q, namely how to reference more than one table with one foreign key. A foreign key reference in some other table to the superclass table will also be a reference to at least one of the subclass tables. This seems like magic. Try it, see if you like it.

Upvotes: 1

Jax
Jax

Reputation: 111

From your description it sounds like the best practice should be applied to your user table and not this inbox table. Of course, I don't know your constraints, but if you have 2 or 3 types of users with each type of user in its own table, that is a poor design (again, not knowing your constraints). The preference is to store all users in one table with a column to indicate their type. Then the reference to your inbox table becomes straightforward with both sender and receiver FKs pointing back to the same user table.

Otherwise, you're going to end up using multiple columns to reference each table like you said (UserTypeASenderID, UserTypeBSenderID, UserTypeCSenderID, etc). My preference is to have null FK columns and gain the referential integrity than to implement some other solution and lose the constraints.

Upvotes: 2

Related Questions