CODEWITHSUNDEEP
mysqlsqlselfdatabase-relations
Ciphor
Ciphor

Reputation: 742

How to implement mySQL self-relationship?

How can I create a Message table such as this in mySQL where it stores messages and their responses?

enter image description here

Upvotes: 1

Views: 6460

Answers (2)

CloudyMarble
CloudyMarble

Reputation: 37566

You can create a foreign_key which references the original messageId, but do not forget to allow null values since original messages will not have this key set.

But is'nt it a better approach to have a thread table, and then in the messages table to save the threadId so you can match which messages belong to which thread, the posting time could be a good indicator to identify response messages.

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

You can try this:

create table messages (
    message_id int primary key,
    response_to int null references messages(message_id), -- self relationship
    user_from int not null references users(user_id),
    user_to int not null references users(user_id),
    content varchar(200) not null
);

The first message will have a null value un response_to field.

As a side note, if you are planning to store "conversations" instead of "messages", consider a plain table with a CLOB (character large object) to store a JSON or XML representation of the conversation. It will speed up your queries (if you are always planning to read the entire conversation at once instead of individual messages).

Upvotes: 8

Related Questions