Large Student dimension or new Student Fact table?

Hi all,
at the moment we have a Student Course fact table keyed to a Student dimension as per normal design methods, however additional reporting requests have come up around columns in the student dimension which can have multiple records. (e.g Home/mail/term address , citizenships)

Can you have a student (or customer) schema to cater for multiple instances of addresses, citizenships, cohorts - any issues / drawbacks with this method?

Or would you use a student dimension with multiple instances of the columns (eg Home Address, Mailing Address)

Thanks,
Duncan

There are a few ways to deal with dimension attributes that can have multiple values at the grain of your dimension.

The first is to look at your dimension and see whether it makes sense to change the grain of it to include the multi-valued attribute. In your case that's probably not feasible, because you've got several such attributes and a dimension with one row per student, address, citizenship and cohort doesn't really make sense.

The second is to move the attribute into its own dimension and then either join it directly to the fact table (if it makes sense to do so) or link it back to the original dimension with a bridge table. One consideration here is whether you're talking about a single dimension attribute, or a group of related attributes. It may make sense for you to create an address dimension.

Third, you can create multiple columns in your dimension to hold the various values. So, you could have PrimaryCohort, OtherCohort1, OtherCohort2 etc. This is only worth doing if the possible number of different values has a small upper bound, say 3 or 4 at the most. And be aware that it can make queries trickly later on.

Hope that helps.