Date: 02/03/06 (MySQL Communtiy)    Keywords: database

I'm working on a site for a medical/nutritional company with custom "client" and "admin" sections, each with a few different features; one thing I built in was a very simple messaging system for clients to communicate back and forth with staff members (kind of a mini-emailing system)

The way I initially set things up, I have information about staff users and client users stored in 2 different tables, as up until now they were 2 separate elements and I'm storing slightly different information on each;

What I'm thinking of doing now is expanding the messaging system to allow messaging between staff members as well (we just never thought of doing it until now) but I'm wondering which way I should change my database structure and thought I'd see if I could get some input



Currently I have my messaging and client/admin login setup roughly like this:

client
------
[clientID]

staff
-----
[staffID]

messages
--------
[msgID]
clientID
staffID
fromStaff (this is a 0/1 to determine who the message is to and who it is from)

I'm wondering if I should either add another table of messages to hold staff->staff messaging:

staff_mesages
-------------
[staffMsgID]
fromStaffID
toStaffID
subject
body

Or - throw all my logins into one table (client and staff users) and determine type by adding an extra access level for clients (I have multiple access levels in the staff end but only 1 client access level) and then do all my messaging like:

messages
--------
fromUserID
toUserID
message
subject

any suggestions?

Source: http://community.livejournal.com/mysql/84595.html