Structuring internal mail system

Question

I have two tables in my database:

• Company table (ID, CompanyName, CompanyUsername, CompanyPassword)
• Employee table (ID, CompanyID, Name, Username, Password)

Right now I am designing an internal mail system for which employees can write to each other, but also write directly to the company account.

My internal mail table has these fields:

• ID
• FromID
• ToID
• Message
• ...

Now my problem occurs, when I fill the message table with ID's (From/To) I have no clue if the message is from the company or the employee as the ID might exists in both tables.

What could my solution be?

Update

The example above was to simplify my question.

The employee and company tables does not contain username or password, but an reference to ASP.NET's membership uniqueidentifier for managing logins. As suggested below with using UI's to control the from and the reciever, I go with UI from the ASP.NET Membership controller. Thanks. :-)

Answer 1

You could use two foreign keys, on to company and one to employee and ensure that only one is ever set.

Answer 2

Use a uniqueidentifier for id in the Company and Employee tables and use newid() as the default value.

Alternatively, Merge the tables and add a field to show whether the record is a Company or Employee.

Answer 3

So an email.ToID could refer to employee.ID or company.ID, is that right? You cannot declaratively constrain that email.ToID must exist in at least one of (employee.ID or company.ID). Few solutions:

1. Have IDs in the email table (ToEmployeeId, ToCompanyId), and a check constraint that constrains that only one can be specified on a given email.
2. Have Employee and Company Ids in the same id space, e.g. their Id is mastered in some kind of Contact table, and then company.Id and employee.Id both have a FK referencing to Contact.Id. I don't think this solution is very good since Contact is a pretty lame super type of these other tables.
3. Have a Contact table like (ContactId, EmployeeId, CompanyId). When a mail is spun up to EmployeeId=X, you look for a Contact row with EmployeeId=X. If one doesn't exist, you create it and use the new ContactId. Same goes for Companyid. This does allow you to expand to use other Id sets in the future as well