[GENERAL] PostgreSQL: Database schema for messaging service (similar to facebook)

Hello,

I'm trying to create a messageing service, like in facebook. Basically
a member can write messages to another member. It will have three main
functions. One, basic messaging to another member. Two, notification
from system to a group of members (a list of members), Three, an
update report to a group of members (a list of members).

I was looking over the net for examples and found this schema:
(http://lh4.ggpht.com/arrival123/SJ-XLk2257I/AAAAAAAABhs/eRY9Nd4VLkw/
facebook_emails_model.gif)

But its a little bit odd for me because I don't need to extend from
My_Email_Logins table, actually I do not fully understand why the
schema creator decided to extends the four tables from
My_EMail_Logins.

Also, the schema will have duplicate data for Subject and Message
within the four tables (Mail_Boxes, Sent_Messages, Notifications, and
Updates)

My current schema, which is a work in progress looks like this:

----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------

CREATE SEQUENCE tm_Messages_MessageID_seq;
CREATE TABLE tm_Messages (
MessageID integer NOT NULL PRIMARY
KEY DEFAULT nextval('tm_Messages_MessageID_seq'),
SentDate timestamp NOT NULL
);

----------------------------------------------------------------------------------------------------------------
CREATE SEQUENCE tm_ReceivedMessages_ReceivedMessageID_seq;
CREATE TABLE tm_ReceivedMessages (
ReceivedMessageID integer NOT NULL PRIMARY
KEY DEFAULT nextval('tm_ReceivedMessages_ReceivedMessageID_seq'),
Username varchar(256) NOT NULL
default '',
Subject varchar(128) NOT NULL,
Body text NOT NULL,
MessageRead boolean NOT NULL default
'0'
);

----------------------------------------------------------------------------------------------------------------

CREATE SEQUENCE tm_SentMessages_SentMessageID_seq;
CREATE TABLE tm_SentMessages (
SentMessageID integer NOT NULL PRIMARY
KEY DEFAULT nextval('tm_SentMessages_SentMessageID_seq'),
-- MessageID integer NOT NULL REFERENCES
tm_Messages (MessageID),
ToUsername varchar(256) NOT NULL
default '',
Subject varchar(128) NOT NULL,
Body text NOT NULL
);

----------------------------------------------------------------------------------------------------------------

CREATE SEQUENCE tm_Notifications_NotificationID_seq;
CREATE TABLE tm_Notifications (
NotificationID integer NOT NULL PRIMARY
KEY DEFAULT nextval('tm_Notifications_NotificationID_seq'),
-- MessageID integer NOT NULL
REFERENCES tm_Messages (MessageID),
ToUsername varchar(256) NOT NULL
default '',
NotificationType integer NOT NULL,
FromUsername varchar(256) NOT NULL
default '',
Subject varchar(128) NOT NULL,
Body text NOT NULL,
NotificationChecked boolean NOT NULL default
'0'
);

----------------------------------------------------------------------------------------------------------------

CREATE SEQUENCE tm_Updates_UpdateID_seq;
CREATE TABLE tm_Updates (
UpdateID integer NOT NULL PRIMARY
KEY DEFAULT nextval('tm_Updates_UpdateID_seq'),
-- MessageID integer NOT NULL
REFERENCES tm_Messages (MessageID),
ToUsername varchar(256) NOT NULL
default '',
FromUsername varchar(256) NOT NULL
default '',
Fullname varchar(128) NOT NULL
default,
Subject varchar(256) NOT NULL,
Body text NOT NULL,
MessageRead boolean NOT NULL default
'0'
);

----------------------------------------------------------------------------------------------------------------

Its very similar to the schema in the picture, but with the
My_Email_Logins table renamed as tm_Messages table, holding just the
date.

Can someone give me pointers or share their knowledge of a messaging
system they have implemented? I also found that this schema although
common is hard to google because of the query strings are ambiguous.

PS. I hope at my code will help someone out there as well.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general