Setting up the db for a private messaging system

[sdpurtill]

I was wondering, how would you set up a private messaging system's database. One that I really like is Facebooks... I am clueless when it comes to db architecture. Thanks !

[extrovertive]

* = primary key
toid = id to person to send to
fromid = id who person he/she is sending to
messge = the message
date = timestamp (optional)

For toid and fromid, it's assuming you have another database where a user who have a unique ID. Otherwise, replace toid and fromid w/ whatever name of the people sending the message.

Message(mid*, toid, fromid, message, date)

[robos99]

For me, the easiest way to design a DB structure is to sit down and really think about everything you'd want this script to do and what data you'd want to collect. You write that down, and start to figure out the appropriate names, primary keys, field lengths, data types, and your table structure. A good rule of thumb is to never include something more than once. For example....you'll likely have a table for PMs, including mostly the fields that extrovertive mentioned above, and whatever else you might need. Each row in this table is going to belong to a new PM. So if you have some data that is going to be the same in several rows, it would be wise to put this data into a separate table.

I'm having some trouble thinking up an example using PMs, so I'll give you one along the lines of Ecommerce. If you had an online store, you'd most likely have a user table, where all your user's info is stored. But say you want to start storing information specific to each order, to keep an order history. It would be bad design to have an order history field in your user table, since each user can have more than 1 order. So you'd make a separate table. Same works with your PM situation. Just think about what data might be shared. A user can have more than 1 PM, so that means a table specifically for PMs is required. But what else might a user have more than 1 of? Or what data might be used by more than 1 user? Think of that when deciding what goes in your tables.