I'm building an access database to track companies and their employees

Question

I'm in the process of designing an Access database that I will use to track different people that I interact with and that in the end will interact with each other. Some of the "people" will be lawyers at law firms, accountants at CPA firms, etc. with different projects that various combinations of "people" will be working on. In my initial design I thought about having a different table for each person "type", such as, lawyer, accountant, etc. but there are always instances that various Lawyers have the same firm name but work at different office locations with various telephone numbers and addresses of the same firm name. I'm trying to design the right combination of table and relationships that will see the same "person" have multiple, for example, telephone numbers, office addresses and even assistants and their numbers. In my initial design I have added a new table for each attribute of a single person. This leaves me with a vary large number of table and a spiderweb of complex relationships. Any high level suggestions on how to address the various "people" issue I'm having would be greatly appreciated. Thanks.

Answer 1

It might help to think of the requirement as encompassing "People" and "Roles" as two separate entities.

The AI answer is partway there, but misses a key point.

One person can fill one or more roles.

A legal assistant at Firm A might also be hired as an intern at Firm B at some point. If you care about that history, you can't assign one role to the person as an attribute in the People table.

And, the same is true for the attribute identifying the organization. If you care that Person A worked for Firm C for 3 years, then was hired away by Firm D, you can't put the FirmID into the People table as an attribute.

Now, I do not know whether you care about history or not. If you only consider this to be an electronic rolodex where you can look up current details and do not care about what happened last year, or the year before, then the AI proposed approach is a useful beginning.

So, you decide, what is the business rule here? History or rolodex?

If it is history, you need (for this part of the design) five tables:

• tblPeople
• tblRoles
• tblFirms
• tblPeopleinRoles
• tblPeopleatFirms

The design pattern here is called Many-to-Many: One person can be in one or more roles. One role can be held by one or more people.

The two tables that handle this are often referred to as Junction tables. They have a specific design pattern.

• ForeignKey --> Person
• ForeignKey --> Role
• DateInRole -- the date when this person assumed this role, when Marcos became a partner.

Further searching on the concepts of Many-to-Many relationships and Junction tables should turn up a plethora of detailed discussions to guide you.

Answer 2

Use a small set of subject-based tables and relate them, instead of one table per “type” or per attribute.

A high-level structure that fits this scenario:

1. People table (one row per person)
   • PersonID (AutoNumber, primary key)
   • FirstName
   • LastName
   • PersonType (e.g., Lawyer, Accountant, etc., or a foreign key to a PersonTypes table)
   • FirmID (foreign key to Firms table, if applicable)
   • Other core attributes that apply to all people
2. Firms (Companies) table
   • FirmID (AutoNumber, primary key)
   • FirmName
   • MainPhone
   • Website
   • Other firm-level attributes
3. FirmLocations table (if firms have multiple offices)
   • LocationID (AutoNumber, primary key)
   • FirmID (foreign key to Firms)
   • Address1, Address2, City, State, PostalCode, Country
   • LocationPhone
4. PersonContactMethods table (to support multiple phone numbers, emails, etc.)
   • ContactID (AutoNumber, primary key)
   • PersonID (foreign key to People)
   • ContactType (e.g., Mobile, Office, Home, Email)
   • ContactValue (e.g., phone number or email address)
5. Assistants table (self-relationship between people)
   • AssistantID (AutoNumber, primary key)
   • PersonID (the main person; foreign key to People)
   • AssistantPersonID (the assistant; foreign key to People)
   • AssistantRole / Notes
6. Projects table
   • ProjectID (AutoNumber, primary key)
   • ProjectName
   • Description
   • Other project attributes
7. ProjectPeople (junction) table (many-to-many between People and Projects)
   • ProjectID (foreign key to Projects)
   • PersonID (foreign key to People)
   • RoleOnProject (e.g., Lead Lawyer, Accountant, etc.)
   • Composite primary key on (ProjectID, PersonID) or a separate AutoNumber key.

Key design principles from Access relational design:

• Each table should store data about one subject (people, firms, locations, projects, etc.), not about a specific “type” of person. This avoids many small, overlapping tables.
• Use an AutoNumber primary key in each main table (e.g., PersonID, FirmID, ProjectID) as a unique identifier.
• Use foreign keys in related tables to connect data. For example, FirmID in People links a person to a firm; PersonID in PersonContactMethods links multiple contact methods to one person.
• Use one-to-many relationships for repeating attributes:
  • One person → many contact methods
  • One firm → many locations
  • One person → many assistants (via a self-referencing table)
• Use a many-to-many relationship for people on projects, implemented via a junction table (ProjectPeople).

This approach keeps the number of tables manageable and uses relationships (primary key ↔ foreign key) to tie the data together instead of creating a separate table for each person type or each attribute.

---

References:

• Use a screen reader to create tables in Access desktop databases
• Database design basics
• Video: Get started with table relationships
• Guide to table relationships
• Introduction to tables