Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux