Personal Messaging System in Database

Question

Hello,

I am new to databases, and would like to know how to best store personal messages in a database (with SQLAlchemy).

Because I wasn't sure, I have tried the following table for PMs

pm_table = Table('personal_message', metadata,
     Column('id', Integer, primary_key=True, autoincrement=True),
     Column('from_id', Integer, ForeignKey('user.id')),
     Column('to_id', Integer, ForeignKey('user.id')),
     Column('message', Text),
     Column('sent_at', DateTime)     
)

and then this is mapped

session.mapper(User, user_table, properties={
     'messages_sent': relation(PM, backref='sender'),
     'messages_received': relation(PM, backref='receiver')
})

I get the following error message:

Could not determine join condition between parent/child tables on relation User.messages_sent. Specify a 'primaryjoin' expression. If this is a many-to-many relation, 'secondaryjoin' is needed as well.

From what I understand so far, this is a one-to-many relationship, but with two foreign keys to the same class. Is this even the right approach or should I have two tables, messages_sent and messages_received? Or still something else? Any pointers in the right direction would be very much appreciated.

EDIT:
To clarify, I'm after a good database design pattern to solve this. If somebody can help with SQLAlchemy, that would be great, but by no means necessary.

but the from_id-column would be the user-id of the sender, and the to_id would be the user-id of the reciever?

Exactly.

Your code sample is incomplete.

I tried to omit unnecessary code in this post, seems I went to far. Here are the missing definitions.

class PM(object):

    def __init__(self, from_id, to_id, message):
        self.from_id = from_id
        self.to_id = to_id
        self.message = message
        self.sent_at = datetime.utcnow()

session.mapper(PM, pm_table)



class User(object):

    def __init__(self, username, pwsalt, pwhash, email):
        self.username = username
        self.pwsalt = pwsalt
        self.pwhash = pwhash
        self.email = email
        self.since = datetime.utcnow()
        self.status = 0

user_table = Table('user', metadata,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('username', String(20)),
    Column('pwsalt', String(32)),
    Column('pwhash', String(64)),
    Column('email', String(320)),
    Column('since', DateTime),
    Column('status', SmallInteger)
)

Answer 1

I don't know how to use the program or language you're using, but I can give some hints on the use of two DBs vs. one.

You should use just one DB for the messages. The messages sent would be excactly the same as the messages recieved anyways. I do not understand what you mean by the two foreign keys (my english is not very good), but the 'from_id'-column would be the user-id of the sender, and the 'to_id' would be the user-id of the reciever?

Some more columns that would be smart to include is a 'deleted'-column for both sender and reciever and a 'read'-column for the reciever.

Answer 2

Your code sample is incomplete.

You've omitted any definition for user_table or the User class.

Also, you've omitted the definition for the PM class.

Without the other definitions, it's hard to figure out what's wrong.

Answer 3

I don't know if answering my own question is accepted here, but I figure it's better to have some answer rather than none.

Two foreign keys to the same class didn't work, so I will have to query the database for the users outbox. But at least the inbox can be accessed via user.inbox. Below is the working code.

class PM(object):

    def __init__(self, sender_id, receiver_id, subject, message):
        self.sender_id = sender_id
        self.receiver_id = receiver_id
        self.subject = subject
        self.message = message
        self.sent_at = datetime.utcnow()
        self.read = False
        self.deleted_sender = False
        self.deleted_receiver = False

    def __repr__(self):
        return '' % (self.from_id, self.to_id)

    def markread(self):
        self.read = True

    def delete(self, deleter_id):
        if deleter_id == self.sender_id:
            self.deleted_sender = True
        else:
            self.deleted_receiver = True

pm_table = Table('personal_message', metadata,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('sender_id', Integer),
    Column('receiver_id', Integer, ForeignKey('user.id')),
    Column('subject', Text),
    Column('message', Text),
    Column('read', Boolean),
    Column('deleted_sender', Boolean),
    Column('deleted_receiver', Boolean),
    Column('sent_at', DateTime)     
)


class User(object):

    def __init__(self, username, pwsalt, pwhash, email):
        self.username = username
        self.pwsalt = pwsalt
        self.pwhash = pwhash
        self.email = email
        self.since = datetime.utcnow()
        self.status = 0

    def __repr__(self):
        return '' % (self.id, self.username, userstatus[self.status])

user_table = Table('user', metadata,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('username', String(20)),
    Column('pwsalt', String(32)),
    Column('pwhash', String(64)),
    Column('email', String(320)),
    Column('since', DateTime),
    Column('status', SmallInteger)
)

userstatus = {
    0: 'user',
    1: 'vip',
    2: 'moderator',
    3: 'admin'
}


session.mapper(User, user_table, properties={
    'inbox': relation(PM)
})
session.mapper(PM, pm_table)