Search code examples
sqlemailsysteminternal

Structuring internal mail system

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. :-)

Solution

  • 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.