Search code examples
sqldatabasedatabase-designrdbms

Tables design for a simple messaging system

I got a simple message system where every message has one sender and always exact one receiver which is never the sender. So my design is like follow:

create table user
{
PersonID int,
Name varchar(255)
}

create table message
{
MessageID int,
FromPersonID int,
ToPersonID int,
Message varchar(160)
}

To get all messages of a given PersonID I write:

SELECT MessageID FROM message WHERE PersonID=FromPersonID OR PersonID=ToPersonID

Now I got two question:

  1. Is this the proper (and fasted) way to design that relation.
  2. How is this relation described in a Database Diagram?
Solution

  • Yup, that's pretty much the textbook way to do it.

    Not sure what you mean by "how is it described in a diagram". In a diagram you would draw two boxes, one for each table. Then there would be two lines connecting User and Message, one labeled "from" and the other labeled "to". The exact shape of the boxes and appearance of the lines depends on what diagramming convention you are using.