Search code examples
ms-accessrdbmsdatabase-normalization

Normalization - How do I properly reference a list from one table in multiple fields in another table?

I'm pretty sure this is a basic question, but I'll be darned if I can find an example that helps me understand the proper way to do what I need to do.

In MS Access 365 I am creating a maintenance tracking database. I have a table with basic employee information. Employees can both perform maintenance tasks and verify maintenance tasks. I can set up a one to many reference using the Employee ID PK as a FK in my task log table for who performed the task. Obviously, I can't set up another one to many relationship to a field of who verified the task.

I already have the task details in a separate table which is referenced by the task log table. Am I just being stubborn in not splitting my task log table into task performed and task verified? Is there another way to normalize this data?

enter image description here

Solution

  • Obviously, I can't set up another one to many relationship to a field of who verified the task.

    Yes, you totally can.

    When you drag&drop the 2nd relation, Access will ask if you want to edit the existing relation or create a new one.

    It will display like this, but it is actually 2 one-to-many relations from User to Log.

    Two FK relations