Tables design for a simple messaging system

Question

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:

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

Seems as good a way as any. You might consider naming your user table person. — Dan Bracuk
– Dan Bracuk, Commented Apr 2, 2015 at 9:36

Jay · Accepted Answer · 2015-04-02 21:04:53Z

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.

Aditya Kamat · Accepted Answer · 2015-04-17 10:27:17Z

You can normalize it according to your query.

for the query

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

you can create a normalized structure

create table user { PersonID int, Name varchar(255) } create table message_meta { FromPersonID int, ToPersonID int, } create table message_data { MessageID int, Message varchar(160) }

and fire a query like

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

This will be more efficient. TC

Collectives™ on Stack Overflow

Tables design for a simple messaging system

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related