BugHunterUK BugHunterUK - 15 days ago 5
SQL Question

Storing different types of "messages" in a simple chat application

I have a simple message system: User sends a message to the group.

I'm trying to normalise as much as possible and do things correctly so the schema can evolve if/when needed.

I decided to have a table that stores every message, and a 2 tables for the different types of messages.

But I've found a problem with this. The queries to

message
are meaningless as they don't produce a message. You have to query
message_image
and
message_text
individually. Which makes me think that my model is wrong.

message
-------
+ id (int)
+ group_id (int)
+ sender_id (int)
+ created_at (timestamp)

message_image
-------------
+ id (int)
+ title (varchar)
+ url (varchar)
+ created_at (timestamp)

message_text
------------
+ id (int)
+ message (varchar)
+ created_at (timestamp)


Are there any negatives to doing this?

Answer

Add a foreign key constraint to message that references message_image as well as a foreign key constraint that in message that references message_text. Then you can look for rows in message where those cols <> NULL.