MS Access人员-项目-课程关联数据库设计优化求助

Hey there! The core issue here is that your current Program table is mixing two distinct pieces of data: the course itself (its name, dates, and associated project) and the enrollment record that links a person to that course. That’s why you’re seeing duplicate course details every time someone new signs up—each enrollment creates a new ProgramID with redundant course info.

Let’s fix this with a normalized database design (following 3rd Normal Form, which eliminates unnecessary data duplication):

1. Person Table (keeps your existing person details) #

• PersonID (Primary Key, AutoNumber)
• FirstName, LastName, [add other details like email, phone, etc. as needed]

2. Project Table (unchanged, stores unique projects) #

• ProjectID (Primary Key, AutoNumber)
• ProjectName (Text, set as Unique to avoid duplicate projects)

3. Course Table (NEW: stores unique courses tied to projects) #

This is the critical addition—now each course is stored exactly once, linked to its parent project.

• CourseID (Primary Key, AutoNumber)
• ProjectID (Foreign Key, links to Project.ProjectID)
• CourseName (Text)
• StartDate (Date/Time)
• EndDate (Date/Time)
  Pro tip: Add a unique index on ProjectID + CourseName to prevent duplicate courses with the same name under the same project.

4. PersonCourseEnrollment Table (NEW: tracks who’s enrolled in which course) #

This table only stores the association between people and courses—no duplicate course data here.

• EnrollmentID (Primary Key, AutoNumber)
• PersonID (Foreign Key, links to Person.PersonID)
• CourseID (Foreign Key, links to Course.CourseID)
  Optional: Add fields like EnrollmentDate (when the person signed up) or Status (active/completed) if you need to track extra enrollment details.

Set up these relationships in Access to enforce data integrity:

• Project → (one-to-many) → Course: One project can have multiple courses; each course belongs to exactly one project.
• Person → (one-to-many) → PersonCourseEnrollment: One person can enroll in multiple courses; each enrollment is tied to one person.
• Course → (one-to-many) → PersonCourseEnrollment: One course can have multiple enrollees; each enrollment is tied to one course.

Let’s map your sample case to this new structure to see how it works:

1. Projects:

   • ProjectID=1, ProjectName="Website"
   • ProjectID=2, ProjectName="Car"

2. Courses:

   • CourseID=1, ProjectID=1, CourseName="What is website?", StartDate=01/01/2024, EndDate=15/01/2024
   • CourseID=2, ProjectID=1, CourseName="Website Design Basics", StartDate=16/01/2024, EndDate=30/01/2024
   • CourseID=3, ProjectID=1, CourseName="Advanced Website Development", StartDate=01/02/2024, EndDate=15/02/2024
   • CourseID=4, ProjectID=2, CourseName="Car Maintenance 101", StartDate=01/01/2024, EndDate=10/01/2024

3. Persons:

   • PersonID=1, FirstName="Max", LastName="Stewart"
   • PersonID=2, FirstName="Roger", LastName="Federer"

4. Enrollments:

   • EnrollmentID=1, PersonID=1, CourseID=1
   • EnrollmentID=2, PersonID=1, CourseID=2
   • EnrollmentID=3, PersonID=1, CourseID=3
   • EnrollmentID=4, PersonID=2, CourseID=4
   • EnrollmentID=5, PersonID=2, CourseID=1

Notice how the course details are only stored once, even when multiple people enroll!

To create a report showing all people for a specific project + course:

1. Build a query that joins all four tables:
   • Join Project to Course on ProjectID
   • Join Course to PersonCourseEnrollment on CourseID
   • Join PersonCourseEnrollment to Person on PersonID
2. Add criteria to filter for your target ProjectName and CourseName (or use parameters so users can select these values when running the report)
3. Create your report based on this query—you can include person details, course dates, project name, and any other fields you need.

内容的提问来源于stack exchange，提问作者llafaovz