Have Geek Brain

Structuring the internal postal system

I have two tables in my database:

  • Companies table (ID, CompanyName, CompanyUsername, CompanyPassword)
  • Employee table (ID, CompanyID, name, username, password)

I am currently developing an internal mailing system for which employees can write to each other, but also write directly to the company account.

My internal zip table has the following fields:

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

Now my problem occurs when I populate the messages table with the ID (From / To). I don't know if there is a message from a company or an employee as the ID can exist in both tables.

What can be my solution?

Update

The above example was to simplify my question.

The employee and company tables do not contain a username or password, but an ASP.NET membership link uniqueidentifier

to manage logins. As shown below, using the UI to control and from the listener, I am going with the UI from the ASP.NET membership controller. Thank you. :-)

0


a source to share


3 answers


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



Alternatively, merge the tables and add a field to show if the record is a company or an employee.

0


a source


You can use two foreign keys, one for the company and one for the employee, and make sure only one is set.



0


a source


So email.ToID can refer to employee.ID or company.ID, right? You cannot declaratively constrain this letter. ToID must exist in at least one of (employee.ID or company.ID). Several solutions:

  • The identifiers in the email table (ToEmployeeId, ToCompanyId) and a check constraint that limits what only one can be specified in a given email.
  • Employee and company IDs in the same id space, for example. their ID is processed in some contact table and then in the company. Eid and employee. Both have an FK link to Contact.Id. I don't think this solution is very good as Contact is a pretty lame super-type of these other tables.
  • Create a contact table, for example (ContactId, EmployeeId, CompanyId). When the mail is expanded to EmployeeId = X, you are looking for a contact string with EmployeeId = X. If that doesn't exist, you create it and use a new ContactId. The same goes for Companyid. This allows the use of other sets of identifiers to expand in the future.
0


a source






More articles:


All Articles