×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Messaging between different types of users in different tables

Messaging between different types of users in different tables

Messaging between different types of users in different tables

(OP)
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?

RE: Messaging between different types of users in different tables

(OP)
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

RE: Messaging between different types of users in different tables

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)
noevil
http://www.mrdenny.com

RE: Messaging between different types of users in different tables

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close