Private Messaging System with 3 different users tables

I have three different users tables, and I would like to know what is the best way to create a private messaging system for them to communicate.

I tried to create it with a simple db scheme:

id (int)
from (int)
to (int)
subject (varchar)
message (text)
timestamp (timestamp)
read (bool)
delet开发者_如何学Goed_to (bool)
deleted_from (bool)

But complications instantly arised because of the three users tables, where user IDs in table A can have ID = 1 and another user in table B can have ID = 2

Any ideas on how to create a better DB scheme? Thanks

use "model" column with varchar type to save which user model the user comes from. this way you can have several entries with "2" for example. model + user_id then has to be unique.

e.g.:

User1 3
User1 5
User2 3
...

A hackish solution would be to create a hash for every user and store it in one table to uniquely identify any users. Then, using the hashes, figure out which message was sent to which user.

It most certainly is not a great idea, because it generates unnecessary overhead sending several queries to the database. If possible, create a table to hold all users and using one-to-one relationships create tables for entity specific fields. Your authentication methods would be stored in the user table. Post authentication your system would know, which tables to join together for the required data as described in that single user table.

You could add two fields in your table to store the "table" related to the from and to.

Dunno what kind of db you are using but this problem could be solved easily with table inheritance.