Messaging between different types of users in different tables

[ssruprai]

I want messaging between different parties..
I want to know what is the best way to create tables.

So if the parties were only members and admin.
This is how tables might look like:

members
---------
id | name


admins
------
id | name


messags
--------------------
id | from_id | to_id | subject | detail | date.....


Now problem is how do I join tables.
if a message is from admin I need to join with admin table to find what is the admin name etc.
if a message is from another member I need to self join tables.

If it was a single message I could add another field to messeages like from_type, which may say it is 'Admin' 'Member'. Then I
can first find out whehter to join to member table or admin table and then do the join. But this is not very effecient.
And what happens when I want to display the list of all messages with subject and say if it is a member or admin and then link to their profile using id from that particular table (admin/member etc.).

I also want to keep an option for adding another party who can send messages like and advertiser. Where advertiser too can send message
to users and users can reply back. so advertisers table may be like:

advertisers
--------------
id | name

and I could add this under from_type field in messages table. 'Admin', 'Member', 'Advertiser'

So basically what I want it communication between all the parties, members-members, members-advertsisers, advertisers-members,
admin-members, member-admins.]

For some reason I cannot do this:

UserTable
---------
id | name | usertype
1 jsmith member
2 jdoe admin
3 jbrown advert

I want each type of user to be in different tables.

Is there any efficient solution to this problem?

 

[ssruprai]

I can create different messages tables for different users like admin_messages, member_messages but main problem will remain.

For example if an advertiser sends a messages to admin and also members sends a messages to admin.

admin_messages may have fields

admin_messages
--------------------
from_id | date | subject | detail

but how do I know who this from_id is and to which table, Members or Advertisers, to join to create list of all messages received with name of sender.
even if I add a column say sender_type and store 'Advertiser', 'Member' there

sender_type | from_id | date | subject | detail
------------------------------------------------------
Advertiser | 1 | 2006-03-31 05:05 | test advertiser | testing advertiser
Member | 2 | 2006-03-31 05:05 | test member | testing a member

There is no way, I know, to join it with both tables( Advertisers and Members) to create a list of all messages with subject. like:

Name | Type | date | subject
-----------------------------------
Sukh | Advertiser | 2006-03-31 05:07 | test advertiser
Dukh | Member | 2006-04-01 05:08 | test member

 

[mrdenny]

I'd recommend putting the Admins and members in the same table with a flag denoting which access level they have.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Olaf Doschke]

Like Denny, I'd also recommend to add a usertype field to one general user table. If you really need different tables for admins, members, advertisers, eg for granting access to these tables only for the corresponding user types, then you could make use of 1:1 relationships and define a central user table and 1:1 Admins, Members and Advertiser tables, which only hold the id:

Users
---------
id | name
1 jsmith
2 jdoe
3 jbrown

Members
------
id
1

Admins
-------
id
2

Advertisers
-----------
id
3

Bye, Olaf.