building a simple messaging app for a website

[TechBoyJK]

I'm writing a simple messaging app for a website, so that members can message each other. I've drawn up how I'm going to setup the table structure, and wanted to post it for criticism. However, I would like it to be something that could scale easily. In theory, it should be able to support millions of messages each day, assuming adequate hardware, etc..

I'm not looking for criticism on column names, etc. Just overall structure.

Column names should be pretty self explanatory..

If you have any ideas, please feel free to let me know. I'm going to begin coding around this structure in about 48 hours.....



tbl_member_messages

message_id (int-identity-pk)
message_sender_member_id (int-fk)
message_recipient_member_id (int-fk)
message_subject (varchar-45)
message_body (varchar-1000)
message_is_new (bit)
message_is_read (bit)
message_is_replied (bit)
message_is_flagged (bit)
message_priority (varchar-16)
message_sent_date (datetime-null)
message_read_date (datetime-null)
message_replied_date (datetime-null)
message_referrer_message_id (int-null)

 

[cudzich09]

I want to create the same application as you, but I dont know how to. Is there any chance I can have a copy of your codes when your done with them? PLEASE

 

[TechBoyJK]

Originally posted by: cudzich09
I want to create the same application as you, but I dont know how to. Is there any chance I can have a copy of your codes when your done with them? PLEASE

im coding in cfml?

 

[cudzich09]

never mind, thanks

 

[PhatoseAlpha]

Why is message priority a varchar? Would seem that there are only so many different priority types out there, and a byte and enumeration should be faster.

Additionally, since messaging systems typically chain, you might be able to replace the date-replied with a reference to an id number of the reply. The replied date would then be accessible by the sent date of the referenced records, and might give you a simpler way to show the messages in their conversational history.

 

[TechBoyJK]

Originally posted by: PhatoseAlpha
Why is message priority a varchar? Would seem that there are only so many different priority types out there, and a byte and enumeration should be faster.

Additionally, since messaging systems typically chain, you might be able to replace the date-replied with a reference to an id number of the reply. The replied date would then be accessible by the sent date of the referenced records, and might give you a simpler way to show the messages in their conversational history.

Yea, the message priority was set as varchar because I was going to use text descriptors that would be exactly what the priority was aka "normal", "important" etc.

So you suggest just giving each value a code number like, normal=1, and use a byte value , which would give me 8 possibilities?

Good idea, since the table already includes a column for the referrer message, which would be the id of the previous message in the chain, should I also include a column for the replied message? Then the table would include a record of both the previous message and next message in the chain, and the values would be null otherwise.

Would it be overkill though to have both previous and next entries for each message, when if you then queried the "next message" using the data from the original message, you would get another record that included the same data?

 

[PhatoseAlpha]

How many priorities you get with a byte will depend on how you use it, and if something can have multiple priorities. You can either use the byte as 8 individually toggleable priority flags, or a single one with up to 256 values.

Whether or not having both is overkill depends on how you expect it to traverse the data. If you're going to be reliably starting at the first message in a chain, then only the reply number is needed. If you'll be starting reliably from the last, the only the previous message is needed. If you could start at either/or or in the middle, then it's more a question of do you expect to have problems with cpu time doing the traversals, or with data storage space for holding both.

 

[TechBoyJK]

Originally posted by: PhatoseAlpha
How many priorities you get with a byte will depend on how you use it, and if something can have multiple priorities. You can either use the byte as 8 individually toggleable priority flags, or a single one with up to 256 values.

Whether or not having both is overkill depends on how you expect it to traverse the data. If you're going to be reliably starting at the first message in a chain, then only the reply number is needed. If you'll be starting reliably from the last, the only the previous message is needed. If you could start at either/or or in the middle, then it's more a question of do you expect to have problems with cpu time doing the traversals, or with data storage space for holding both.

well, i was thinking it would be best to log both the previous message AND reply message id in the record, since I would only need to make a single query to get both. Otherwise, I would have to make two queries to get the information... but then again, if I DID need both, i would suspect I would be needing that previous/reply message anyway, so two queries might be needed anyway...

i dunno... i need to sleep on it

 

[aceO07]

How about:

Users - table
user_id
user_name
etc

Messages - table
message_id
message_content
etc

User_Messages
user_id
message_id
sent_date
read..
etc

This way you can send messages to groups of recipients and not have to duplicate the message contents and other misc properties.

 

[JACKDRUID]

message_is_new (bit)
message_is_read (bit)
message_is_replied (bit)
message_is_flagged (bit)

could be consolidated to "status" smallint

 

[George P Burdell]

Originally posted by: JACKDRUID
message_is_new (bit)
message_is_read (bit)
message_is_replied (bit)
message_is_flagged (bit)

could be consolidated to "status" smallint

True, but will the OP want to sacrifice readability for efficiency?

 

[Evadman]

Originally posted by: George P Burdell
True, but will the OP want to sacrifice readability for efficiency?

it would be more efficient in almost all instances using 4 bits than a small int, which is 2 bytes. (4 times as large). Bits can't be included in indexes though without tricks.

Pros to bits:
Smaller row size
easier readability
higher efficiency in most queries

pros to smallint:
less readability
larger row size (remember, millions of rows per day)
can be included in indexes
can add additional status codes without adding columns

 

[NiKeFiDO]

instant messages? (usually have xml-based solutions)

or more like private message style solutions?