Hoping some kind folks might help a relative newbie to Access review my relationships diagram and help me sort out normalization issues, including use of a junction table (or two). Just want to see if I'm on the right track before I get too ahead of myself.
Attached is the relationships diagram, but here is a description of the database and the tables:
Essentially, my project is to track agreements between colleges and schools in which college credit is given to high school students taking particular courses. At times, multiple courses on both sides (college or high school) can be in an agreement (called an Articulation) - so not just one-to-one. As of now, I have a junction table to pull together the respective courses (or set of courses) and then that table links to another table for the details of the agreement.
There are other supporting tables for lookups and such, but the above are the main tables/elements at play.
Do I need to / should I split the Courses table in two (one for College and one for School)? Some fields are 'equivalent' whereas things like Units are not. Also, a couple fields are relevant only to Schools and not to Colleges.
Is my junction table set-up logical, necessary, or am I making this more complex than needed? My main thinking was to resolve the Many-to-Many relationship between College and High School courses that feed into the articulation agreements.
Many thanks in advance to whomever can help me wrap my head around this!
Okay, well with some input from another message board and my own thinking about the database, I have revised my tables from the initial post. Attached is the revised Relationships diagram. Any help with normalization would be much appreciated.