CODEWITHSUNDEEP
sqldatabasedatabase-designrdbms
Martin Schlott
Martin Schlott

Reputation: 4557

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?

Upvotes: 0

Views: 1342

Answers (2)

Aditya Kamat
Aditya Kamat

Reputation: 149

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

Upvotes: 0

Jay
Jay

Reputation: 27492

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.

Upvotes: 1

Related Questions