Search code examples
postgresqlcreate-table

POSTGRESQL: Table schema for message with unique id but multiple receivers

I want to create a table for messages that have a unique id. However a client should also be able to send the same message to multiple receivers so I came up with the following table:

CREATE TABLE IF NOT EXISTS messages (
  id SERIAL,
  receiver INT,
  content VARCHAR,
  CONSTRAINT msg_rcv_id PRIMARY KEY (id, receiver)
);

I used SERIAL for the id because every new message shall have a new unique id but actually what makes the msg unique is the combination of the id together with the receiver. So every message now gets a new id even if its the same message but a different receiver. How can I solve this problem?

greets, jan

Solution

  • I would take out the receiver column and create a new table:

    CREATE TABLE receivers (
msgid int REFERENCES messages(id),
receiver INT,
UNIQUE (msgid, receiver)
);

    My syntax might be a bit off, since I haven't touched Postgres in many years, but you get the idea: put the message->receiver relationships in a separate table.