Database relation design

Question

When I develop apps I reach situations like this frequently but I never found a best practice to solve it.

Imagine:

• We have chats, each chat can have many message.

• We have tickets, each ticket can have many message too.

Solution 1:

We create 3 tables: chats, tickets, messages, and we link each chat or ticket to its messages using polymorphic relation ship.

In this solution:

• We have clean database. and we don't use two tables for same kind of data.
• We won't able to use relational database features like cascade delete. so we have to remove the related messages programmatically when we do remove any chat or ticket, we also can use triggers.

Solution 2:

We create 4 tables: chats, tickets, chat_messages, ticket_messages, and we link each chat or ticket to its messages using foreign key.

In this solution:

• Our database is ugly and we use two tables for same kind of data.
• We can use features like cascade delete and etc, this is really good and clean.

Solution 3:

Please you tell...

• Bart van Ingen Schenau: Are chat messages and ticket messages really the same thing? Will they always have the same limitations and capabilities? For example, there is no chance that you might need to keep track of a modification date (or edit history) for a ticket message, but not for a chat message? The mere fact that it is both called "message" is not enough to conclude they are meant to be the same thing.
• Sina Sharifzade: They are message, they are same object, if one message can have updated_at column probably the other also can have that.
• Bart van Ingen Schenau: Are they required to be the same object, or did you just make it that way because both are referred to as messages? Would your design have been different if the terminology used was "chat message" and "ticket comment"?
• Sina Sharifzade: Actually it has nothing to do with naming, it is related to the nature of the comment message and ticket message, cause the nature is same and they are same model they should be in 1 table, even if we call it ticket comment it won't change anything cause we can also use comment for chat, like chat comment, cause they have same nature.
• James Anderson: You are going to a lot of trouble just to avoid an extra table! Its very likely that chat messages and ticket messages will end up with a completely different set of business rules even if they are the same at the moment. Presumably a ticket has more rules and constraints than just a chat and its likely that there will be more rules regarding associated messages. Its a common misconception but MORE tables is usually a sign of a better more accurate relational model.

Answer 1

You can use two different foreign keys in messages referring to chats and tickets, respectively. The intended usage is that one of these foreign keys has a value and the other has NULL. The cascade delete will work from either table.

The main drawback is we cannot specify non-null for the foreign keys, so it will be possible to insert into messages with both foreign keys NULL, which is essentially an uncaught error in usage. (or both non NULL, which is also an error in usage.)

(For this and numerous other scenarios, it would be great if SQL offered column grouping such that exactly one member of a column group had to be not NULL, but alas this is an omission in relational algebra.)

---

CREATE TABLE products
( product_id INT PRIMARY KEY,
 product_name VARCHAR(50) NOT NULL,
 category VARCHAR(25)
);
CREATE TABLE products2
( product_id2 INT PRIMARY KEY,
 product_name VARCHAR(50) NOT NULL,
 category VARCHAR(25)
);
CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
 product_id INT,
 product_id2 INT,
 CONSTRAINT fk_inv_product_id
 FOREIGN KEY (product_id)
 REFERENCES products (product_id)
 ON DELETE CASCADE,
 CONSTRAINT fk_inv_product_id2
 FOREIGN KEY (product_id2)
 REFERENCES products2 (product_id2)
 ON DELETE CASCADE
);
insert into products (product_id,product_name,category) values (1,'item1', 'general'),(2,'item2', 'general');
insert into products2 (product_id2,product_name,category) values (4,'item4', 'general'),(5,'item5', 'general');
insert into inventory (inventory_id,product_id,product_id2) values (10, 1, NULL), (20,2,NULL), (40,NULL,4), (50,NULL,5);
/* these two inserts are usage error, not caught */
insert into inventory (inventory_id,product_id,product_id2) values (1000,1,4), (1001,NULL,NULL);
select * from products;
select * from products2;
select * from inventory;
delete from products where product_id = 1;
select * from products;
select * from products2;
select * from inventory;
delete from products2 where product_id2 = 4;
select * from products;
select * from products2;
select * from inventory;

https://rextester.com/l/sql_server_online_compiler

• Sina Sharifzade: It seems we have something called CHECK in SQL so we can control to only allow one of the fields have value. it seems it is supported at MariaDB +10.2.1. I think your answer should be accepted, I'll just wait a little more.

Answer 2

The clean way to do this is to create five tables: chat, tickets, messages, chat_messages and tickets_messages. The last two are called linking tables, and they only contain two columns: a FK reference to the parent table and an FK reference to the child table. A query would look like this:

select whatever_fields_you_want
from TICKETS t
join TICKETS_MESSAGES tm on t.ID = tm.TICKET_ID
join MESSAGES m on m.ID = tm.MESSAGE_ID
where T.ID = :id_to_search_for

• Sina Sharifzade: This solution is called many to many relationship, if I use this solution I won't be able to again use cascade delete feature, polymorphic relationship (which I mentioned at the q) I think is far better in this situation than many to many.
• Sina Sharifzade: Many to many is for when you child can act independent, and by removing parent you don't need to remove child, like post and category, in my case it is not like this.

Answer 3

You can do it with 3 tables: ticket_type, tickets, messages. Table ticket_type shoud have 2 records, ticket, chat. Table ticket should have filed ticket_type with foreign key to table ticket_type.

This way it is easy to extend to new types of tickets.

• Sina Sharifzade: Chat and Ticket are not same object.
• Daniel Vidić: If you create new table for every new class you will have hundreds of tables. It is your decision if classes are enough similar to be stored into same table and distinguished by type and some fields.
• Sina Sharifzade: It is not related to similarity, it is related to nature of objects, Chat and Ticket is not same, even their fields were 90% same.
• Daniel Vidić: From your point of view Solution 1 is a way to go.
• James Anderson: You can do it with One table if you really want to -- With columns type, attribute, key value. You can model anything, its just not a very nice model! Keep different thing in different tables, there, is no advantage in lumping all the chat messages, with ticket messages just because they look similar.