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...

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

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

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.