Hi all. I am new to UtterAccess, and could use help with a generic database problem...

I am building a website and I want to allow Users to send and receive Private Messages. (Not exactly new technology - but new to me!!) (IMG:style_emoticons/default/blush.gif)

What would help me the most is if we can have an *open* conversation on this, because honestly, I'm not exactly sure what I want until I know what is involved in creating it?!

Here are some things I do know about Private Message (PM) systems...

- It is made up of "Users"
- It has "Messages"
- It has "Senders" (of Messages)
- It has "Recipients" (of Messages)
- It *must* allow one User to send Messages to another User
- It *could* allow one User to send one Message to many Users
- It *could* introduce the concept of a "Conversation" which is made up of several Messages
- Senders *must* be able to see all Messages they have sent
- Recipients *must* be able to see all Messages they have received (i.e. "Inbox")
- Recipients *must* be able to see all Messages they have responded to (i.e. "Inbox")
- Messages *should* be able to "deleted" (from a User's standpoint)


I am fuzzy on what my Tables should be as well as the Relationships?! (IMG:style_emoticons/default/iconfused.gif)

It would probably make sense to first come up with a Database Design for the simplest PM System which would just allow one Sender to send Messages to one Recipient.

I am hoping I am not asking for too much help here?! (IMG:style_emoticons/default/frown.gif)

Sincerely,


Rob

P.S. Since I will be doing this on the Web, what database I use shouldn't be a consideration here.

Hi Rob,

(IMG:style_emoticons/default/welcome2UA.gif)

Is this for a business, school project, or just plain fun? I only asked because I can't imagine you really wanting to "reinvent" the wheel on this because I think there are some "off-the-shelf" software or freeware apps you can use to get this functionality. If you will be writing the script yourself, what language will you be using?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

tagging onto what theDBGuy says, here's an example of one.

I am doing this for business, for personal, and for fun!! (IMG:style_emoticons/default/smile.gif)

Thanks for pointing out the off-the-shelf stuff, but that would take all of the fun out of it!

I am new to web development, and really am enjoying the journey of taking what I knew in the client/server world and applying it online. (Much different than how you'd do it in MS Access!!)

Since you ask, I am using PHP and MySQL, but again, I have hand-coded my entire website and prefer to stay that way. (I hand-coded my entire User Account system and users can now register, activate accounts, log in, log out, change password, reset passwords, upload pictures, etc. Hopefully adding PM functionality won't be too much harder...)

Think you can help me out?

Sincerely,


Rob

George,


I'm skimming the link you provided, but since I do not know object-oriented programming, that's not much help. (Very confusing!!)

My goal for this thread, is just to talk about the database end and how to properly model things. (I'll figure out the coding part later.)

Sincerely,


Rob

I picked that link because it includes a description of the tables needed for the PM system it talks about.

Coincidentally the tutorial in that link IS based on PHP5, which you have already used to create your current website. "I am using PHP and MySQL". So, unless I'm missing something here you SHOULD be familiar with the methods in that link?

As theDBGuy said, unless you want to do this as an exercise in learning how to do this, it's probably more effective to find and implement an existing PM application.

Nonetheless, a bingoogle search on the term "schema for a Private Messaging Database" ought to bring up a number of potential sources of informtion.

All the tutorial mentions is ONE table?! (IMG:style_emoticons/default/confused.gif)




Like I said, I do NOT know object-oriented programming. So no, I did not understand any of the PHP code. (Having an MS Access/VBA background, I am used to coding in an old-fashioned procedural way. The OOP stuff is over my head and not something I want to undertake anytime soon.)




My goal is to learn how to build this from scratch. And while I don't mind leveraging what others have done, I came here to find people willing to talk with me and help me to understand why certain design decisions are made. (Just copying other people's code and database schemas won't help me learn...)

From what I have Googled, most people just have one table, and that blows my mind?!

Certainly you would need a "member" table and a "message" table. And I am unsure if "members" need to be broken out into a "sender" table and a "recipient" table. And do "messages" need to be broken up into a "sent" tbale and a "received" table?

See, this is the kind of stuff I am not understanding, and I was hoping that by talking with others who are more experienced modeling data, that I could get a better understanding of not just the What's but the Why's...

Sincerely,


Rob

Well, if you find that the typical "simple" PM systems out there are not adequate for your purposes, you can design a schema that does better suit you. It is just that when I am starting with a new project, I often find it's handy to look at what others have done before to avoid reinventing the same wheel, so I thought you might get some ideas from previously created PM applications that worked.

Based on what you've described, though, I would say you could have a "member" table and a "message" table. That would allow you to track members of your "group", whether they participate in PM's or not. In the "message" table you would record things like the MemberID of a person who initiated a message, and the MemberID of the person to whom it was sent. (Call those fields "Sender" and "Receipent".) Then you'll probably want a field to capture the date and time the message was sent. Finally you'll need a field for the body of the message.

Other things might well be useful, including a "linked MessageID" field in the Message table, so you can track responses to messages back and forth.

Beyond that, you can probably think of additional things you'd like to add, but for a basic PM system, that ought to be good enough to start with.

Give it a couple of days. I'm sure you'll get additional input from other developers who have had to create a PM system and who can offer "do's" and "don'ts" from their experience.

George,



I don't disagree, but every example that I have seen has ONE table... That seems too simple?! Maybe I'm making this harder than it is, but one table just seems so "denormalized"?




Well, I already have a "member" table for accounts, logging in, etc.




Again, I don't disagree with any of that. I came to my own same conclusion yesterday.

I think what is confusing me the most is how to abstract a "Sender" and a "Recipient".

Are they both "Users" and thus in the same table?

Do I need two tables?

And is there just one "Message"?

Or is there a "Sent Message" and a "Received Message"?

If I was drawing an ERD, what would it look like?

It seems like I have a many-to-many here somewhere?

Maybe...

Sender -||-------0< Message ->0---------||- Receiver


Or is it...

Member -||-------0<- Message


Or is the Member table just a "lookup" like this...

Member (Sender) -||--------0<- Message

Member (Recipient) -||------0<- Message


See how I am getting confused?!




When I build things, I'd rather build a 5 bedroom house and only have 2 kids, than the opposite!! (IMG:style_emoticons/default/laugh.gif) SO based on that mindset, how much more work would it be to add the concept of a "Conversation" (or "Thread") to my PM System?

That way, as you allude, if you and I are PM'ing about your fishing trip, then they can be linked together.

If I did that, I would think I would need a "Conversation" (or "Thread") table.




That is what I hope to get from here that I can't find as easily Googling.

Thanks,


Rob

Why does one table seem too simple? What is YOU want that can't be handled in a single table? The point here is that there is no universally applicable set of rules for Private Message applications (just as there are none for any other type of database). The business rules you decide to enforce determine what you have to do to enforce them.

So the question is why do you think you want to put senders and recipients in different tables? What does that gain you?

You have MEMBERs. They send each other PM's. Sometimes Member A starts the exchange with B. Sometimes Member B starts the exchange with A. In other words, the same member has more than one ROLE, but they are all members. Their roles ("sender" and "recipient") are only valid within the context of a single PM exchange; they are not universally defined by either role.

If Member A starts an PM, their ID goes into the "Sender" field in the Message table. Whoever receives that PM is the recipient and their ID goes into the "Receipient" field in the message table. It's pretty much that straightforward.

However, if you decide that a PM can be sent to multiple recipients, you need to design this as a many-to-many relationship so that a single person can create a message and send it to multiple recipients. It's really up to your rules.

Now, if you decide to add a "threaded conversation" component, you can do that as well. If you want to support threaded conversations, you can add that table and link individual messages to conversations.

Again, it's up to you to decide what you need in your system to support your goals.

George,

Okay, good, this is the kind of conversation I was hoping to have. (You're getting the rust off of my brain!)




Well, I am uncertain about the Sender and Receiver, but I'll get to that. For just a one Sender, one Message, one Recipient, I guess at least one Message table will do (i.e. no Thread table)





I understand that.




Well, as stated above, because in my mind I have this...

Member (Sender) -||---------0<- Message

Member (Recipient) -||---------0<- Message

...where the "Member" table is being joined to the "Message" table TWICE.




Okay, I'll give you that.




So how do you draw that in an ERD?




Okay.

And where do I keep track of...

- If the Recipient Read the Message?
- If the Recipient Deleted the Message?
- If the Sender Deleted the Message?




Is that a feature I should or would want to have?

What does UtterAccess do?




What do you think about this?

I know that everyone is "hot and bothered" over Facebook and what Facebook does, but how useful is having "Threaded Messages" or "Threaded PMs"?? (Personally, I've never had a Facebook account, so I have no clue what it looks like or how it works.)

If I build a User Forum, then obviously the concept of a "Thread" is needed, but for PM's I'm thinking it is overkill?!

What do you think, George? Anyone else?

Sincerely,


Rob