I have a database with some users and agents and want to add a simple message system.
So I have the following simple set of tables:
[users]
- user_id (PK)
[agents]
- agent_id (PK)
[message_threads]
- message_thread_id (PK)
[message]
- message_id (PK)
- message_thread_id (FK messages_threads.message_thread_id)
What I don't have is a relationship from the message and the individual who posted the message.
There is where I'm a bit stuck, because it could be a user or an agent. I figure this must be a common problem with a accepted pattern to solve, but I haven't found such a discussion.
I know I have a few options, but they all have cons.
Option 1: I don't like that a message could be linked two to different accounts.
[message]
- message_id (PK)
- message_thread_id (FK messages_threads.message_thread_id)
- user_id (FK users.user_id, ALLOW NULL)
- agent_id (FK agents.agent_id, ALLOW NULL)
Options 2: This make it awkward to get all messages in one column of a SELECT.
[message_by_user]
- message_id (PK)
- message_thread_id (FK messages_threads.message_thread_id)
- user_id (FK users.user_id)
[message_by_agent]
- message_id (PK)
- message_thread_id (FK messages_threads.message_thread_id)
- agent_id (FK agents.agent_id)
The one thing I can't do in combine users and agents into one table. That is set in stone.
It sounds like users and agents are each subclasses of a superclass that I'll call "persons". You could have one more table for persons, with person_id as the PK. You could then replace user_id with person_id in the users table. Likewise, replace agent_id with person_id in the agents table.
notice that in the two subclass tables, users and agents, person_id does double duty. It's the PK for its own table, and also an FK to the persons table. This enforces the one-to-one nature of the IS-A relationships between users and persons and between agents and persons.
Now, all you have to do is set up a relationship between messages and persons, and hey presto! There is a relationship between the message and a user or an agent as the case may be.
This is one instance of a design pattern called "Class Table Inheritance" and one called "Shared Primary Key". You can get more info on these by visiting the tags with the same name here in Stackoverflow, or by searching the web for Martin Fowler's treatment of the topic.