How to design a one to many relationship

I have a student quarterly registration fact table. Every student has academic programs (majors) associated with him/her and each student can have up to 5 of them. And each major can start and end at different times and has multiple parameters including graduation information. My fact table will have these dimensions:

Time, Quarter, student, registration parameters, student programs, majors, etc.

How do I model the majors dimension? Do I list them as major1, major2, major3, major4, major5 with their parameters or do I create a new record for each one?

Thank you,
Diana

The usual way to handle this is to make a separate fact table for student-major, with a row per either student-major (update the end effective date, when it occurs, on the existing row) or a row per change (so a new row is appended to specify the end date for a student-major). The update-in-place design is easier to report from, but some DBA's and/or ETL managers don't allow updates to fact tables, in which case you have to refresh the entire table to "update" selected rows, or go the append route which makes reporting a little more complex.

The "primary major" would be the only one on the registration fact table -- primary major is required for a number of types of external reporting that most higher ed institutions participate in; there should be a rule already known for determining primary major.

If reporting needs to combine registration facts with student-major information including non-primary majors, with conformed dimensions and a decent reporting tool you can query the 2 fact tables and combine the results; see Kimball's writings plus other BI references. If there are registration facts that are frequently used with student-major you can figure out if they can be carried in the student-major fact table, which otherwise may be a "factless" fact table in Kimball terms; possible contents [dates are foreign keys to a date dimension]: student key, major key, major type key, begin date, end date, degree awarded date.

Of course, you need to be careful of double counting and other "unequal grain" pitfalls when combining registration and student-major data.

Good luck!

In addition, if there is a need to perform analysis on combinations of majors (such as: how may students are Computer Science majors that are also Economics or Theatre majors) you may want to model it as a multivalued dimension.

You have a junk dimension which contain every unique combination of major (no need for attributes, just the major identifier). This dimension identifies a major group. The student is assigned to that group (fk of the group). There is a factless fact table of group keys and major keys (to a major dimension) which enumerates the majors in the group. The major dimension contains all the necessary major attrbutes.

The group/major table can be used in place of the student/major table in the previous post. But it can also be used to quickly isolate groups that have similar combinations of majors. The group/major table will also be much, much smaller than the student/major table, as it is typical that a large majority of students take similar programs.