This is worth explaining in detail. The classic way to model student registrations or enrollments in courses is to use three tables:
- Courses
- Students
- Enrollments
The tables are related as follows:
Courses -<
Enrollments >-
StudentsIn other words,
Courses have multiple
Enrollments and
Students have multiple
Enrollments. When documenting parent child relationships it is best to use a "
crow's foot" extending from the
parent to the
child table instead of an arrow as an argument can be made for the arrow to point in either direction (depending on what you consider the arrow pointing to). When configured in this fashion, the
Enrollments table is called a
Join table as it relates which
student is taking which
course.
Now consider this sample data:
Students[Record ID#], [Name], [Email]
1, Larry,
larry@example.com2, Moe,
moe@example.com4, Curly,
curly@example.comCourses[Record ID#], [Name], [Hours]
2, Sweeping, 10
3, Plumbing, 12
5, Painting, 15
Enrollments[Record ID#], [Related Student], [Related Course], [Date], [Location]
11, 1, 2, 1/17/17, Des Moinse
12, 1, 3, 2/14/17, New York
13, 2, 3, 1/15/17, Chicago
15, 4, 3, 3/15/17, Boston
20, 4, 5, 5/23/17, Los Angeles
If I haven't made a typo, this table data corresponds to the following narrative information:
Larry took Sweeping on 1/17/17 in Des Moines
Larry took Plumbing on 2/14/17 in New York
Moe took Plumbing on 1/15/17 in Chicago
Curly took Plumbing on 3/15/17 in Boston
Curly took Painting on 5/23/17 in Los Angeles
Note that:
[Name] and
[Email] are unique properties of
Students;
[Name] and
[Hours] are unique properties of
Courses; and
[Date] and
[Location] are unique properties of
Enrollments.
In our case the
Join table
Enrollments has its own fields
[Date] and
[Location]. However, in a simpler situation a
Join table may only have the two "related" fields if we were not tracking any further detail about the enrollment other than which
student took which
course.
Note that the
[Record ID#] values for
each of the three tables are
not relevant to the relationships of which student took which course. Also note there are gaps in our
[Record ID#] values as the result of deletions or perhaps that some students or courses are no longer active so we don't have to keep all records in an active context. The only thing that is important in regard to the
[Record ID#] values is that the
[Related Student] and
[Related Course] consistently point to the correct record.
Now let's say a new
Student (say Shemp) comes along or a new
Course (say Barbering) is added. When this student or course is imported into a table a new
[Record ID#] is created in the appropriate table representing this new entity. If you are importing new registration information (say Shemp took Barbering on 5/6/17 in Detroit) you don't know at the time of the import what values to assign to the fields
[Related Student] and
[Related Course] in the
Enrollment table because the enrollment may be referencing a new
Student or a new
Course which was simply assigned a
[Record ID#] value seemingly at random. To get around this difficulty it is common to create
key fields in each of the tables that uniquely identify each record by an ID that has a meaning
outside of QuickBase. Consider this such schema:
Students[Student ID], [Name], [Email]
L120, Larry,
larry@example.comM345, Moe,
moe@example.comC446, Curly,
curly@example.comCourses[Course ID], [Name], [Hours]
SWP101, Sweeping, 10
PLM201, Plumbing, 12
PNT101, Painting, 15
Enrollments[Record ID#],
[Related Student],
[Related Course], [Date], [Location]
11,
L120,
SWP101, 1/17/17, Des Moinse
12,
L120,
PLM201, 2/14/17, New York
13,
M345,
PLM201, 1/15/17, Chicago
15,
C446,
PLM201, 3/15/17, Boston
20,
C446,
PNT101, 5/23/17, Los Angeles
Now if the new student
Shemp (with a Student ID of
S987) registered for a new course
Barbering 301 (with course ID of
BRB301) on
5/6/17 in
Detroit we could import the following enrollment data and the relationships among the three tables would be appropriately created:
S987, BRB301, 5/6/17, Detroit
Sometimes instead of created a new key field such as
[Student ID] it may be more convenient to use some other unique identifier for a student such as
[Email] or
[Phone].
Now you may be wondering why I didn't create a key field for the
Enrollment table. In a lot of cases you may not need a pinpoint reference to a
Join table record because you will never be referencing an enrollment without already having a specific student and course in context. But if you had a lot of enrollments and needed to reference and manage them you may well create a special key field for the enrollment table that somehow encoded the enrollment's
[Date] or
[Location] information and had meaning outside of QuickBase. Perhaps something like this:
Enrollments[Enrollment Key], [Related Student], [Related Course], [Date], [Location]
DM011717,
L120, SWP101, 1/17/17, Des Moinse
NY021417, L120, PLM201, 2/14/17, New York
CH011517, M345, PLM201, 1/15/17, Chicago
BN031517, C446, PLM201, 3/15/17, Boston
LA052317, C446, PNT101, 5/23/17, Los Angeles
Well I hoped this information helps you.