Database design: what to do about a table that can be referenced by two other tables?

Question

I have a table called WorkItemNotes, and separate tables for Customers and Employees.

I am using MySQL WorkBench to map out the database tables and relationships and have a couple of questions.

Both Customers and Employees can add notes to the WorkItemNotes table, and the associated ID will be stored in the table:

| WorkItemNotes |
| guid/ID       |
| Notes         |
| Author        |

Am I better to have two tables for notes, one for each the Customer and the Employee? Or if I keep one table, how can I set up the relationship in WorkBench. Is it valid to attach a foreignkey to the Author field that is reference by both the Customer and Employee table?

Answer 1

You can do something like this

| WorkItemNotes |
| guid/ID       |
| Notes         |
| Author        |
| AuthorType    |

add one more field in table as AuthorType will do work for you

• AuthorType =1 Customer
• AuthorType =2 Employee

Answer 2

I would probably try to do this instead.

Have a generic users table with UserID as ID Maybe you can add a column called User Type which can be Employee / Customer / others

Link this user id to WorkItemNotes as a FK.

Have 2 separate Employee and Customer Tables Each of them has a FK reference to the UserID of users table with customer / employee specific information

Answer 3

Have one table for your notes. The primary key in the notes table, say note_id, will be stored in your customers and employees table.

**Note**
note_id
notes
author

**Customers**
customers_id
note_id NULL
...

**Employees**
employees_id
note_id NULL

Have a FK between employees and notes, and customers and notes. This design only allows for 1 note. So if you need n notes, then you need a link table such as:

**Notes**
external_id
external_type
note_id

external_type=1 for customers, 2 for employees. the external_id is either the cusomter_id or the employee id.

This design allows for many different tables taking notes. If you only have two tables that can store notes, then simply use two note tables, but this design allows for many. However, because of the link table being able to join to any customer or employee table, you cannot use FK's.

Answer 4

The cleanest solution is to model the existence of a Person as a fact, distinct from their status as a Customer or Employee, and tie the Notes to the Person relation:

Person { PersonID PK, ... }
Customer { PersonID PK FK(Person.PersonID), ... }
Employee { PersonID PK FK(Person.PersonID), ... }
WorkItemNotes { NotesID PK, Notes, AuthorID FK(Person.PersonID) }

This has the additional feature (or misfeature, depending on the business rules) that an employee can also be a customer.