How do I add mulltiple records and avoid using multi-field values?

I'm creating a database for personnel records and trying to ease record creation for the user and avoid a kludgy solution. The tables are:

people: people_id, person_name, person_category_id

person_category: person_category_id, person type

document_requirement: document_requirement_id, document_requirement_name, person_category_id, document_section_id

document_section: document_section_id, document_section

I've created an append query (inner join) that populates a table caLLed document_repository which contains all of the required documents for all of the people. (I use a primary key composed of people_ID & document_id to avoid duplicates when the append query runs.) Here is the document_repository table.

document_respository: document_repository_id, people_id, person category_id, document_id, document_section_id, document_attachment

I'd like to be able to allow the user to create a document requirement that is applicable to multiple person categories. I understand I should avoid multi field values, which doesn't work anyway with inner joins. For example, if people categories include doctors and nurses, I'd like to be able to create a new document requirement that applies to both people categories (e.g., doctors and nurses), without having to create two separate document requirements.

More information needed?

Suggestions on design changes and/or queries?

Thanks!

snapshot of tables and relationships

Solution

What you describe is a many to many relationship. Each document requirement can be applicable to multiple person categories and different document requirements can be applicable to the same person category.

To have a many to many relationship between two entities (tables) in your database, you need another table to relate them. This additional table contains the primary key of both tables and each record in this table represents a link between the two entities.

Your naming is different between your text and your diagram, but I'll assume you want to have document_requirement records that can link to zero or more person_category records.

You need a table which for example could be called document_requirement_person_category and contains the following fields:

document_requirement_id - foreign key referencing PK of document_requirement
person_category_id - foreign key referencing PK of person_category

You then add a record to this link table for each person category that relates to each document requirement.

Edit: BTW, (if I'm reading your schema correctly), you already have a many to many relationship in your schema: document_repository allows a relationship between multiple people and a document requirement as well as multiple document requirements and a person. That's a many to many relationship.