How to implement mySQL self-relationship?

Question

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

Answer 1

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