Faculty Course Relationship

Guys,

I need your suggestions on how to handle the relationship between student course registration and faculty.

A student is registered in a section/courses which is essentially a fact table. A student can register in more than one course/section.

The section/courses can be taught by one or more instructors at different timings though.

How do I model a relationship between the student registration and faculty which are assigned to teach those sections.

Does Faculty assignment to section sound a fact table or dimension. If its a dimension table what would the surrogate be like.

appreciate your help!

The section/faculty relationship is handled with a bridge table. It may contain an allocation factor if needed for contribution or FTE calculations.

BTW - Kimball's book has a whole chapter covering this topic of students/courses/faculties (Chapter 13 - Education). I would definitely recommend that you read it

Guys I am not sure If I quite I understand this yet. Adding a instructor group key in this table which would be same if 2 instructors are teaching the same section, however this group key cannot be a PK in this bridge table for sure,its an FK so what is the corresponding dimension table for the instructor group key. I understand that the bridge table is perfect for my scenario where I have a many to many relationship between my fact and dimension table. Are there any standards for naming bridge tables. e.g. Dim, Fact or _DM or _FT. I have also attached the article below.




add a bridge table with an instructor group key in either the fact table or as an outrigger on the course dimension, as introduced in Chapter 8: Customer Relationship Management. There would be one row in this table for each instructor who teaches courses on his own. In addition, there would be two rows for each instructor team; these rows would associate the same group key with individual instructor keys. The concatenation of the group key and instructor key would uniquely identify each bridge table row.

The PK on the Bridge table (whether implied or explicitly defined) is the "Instructor Group Key + Instructor Key". There is no Instructor Group Dimension, the Instructor Group Key is just an artificial construct to enable a M:M join between the Fact and the Instructor Dimension.

At the risk of confusing things, an extension of this model would be if your business does have the concept of an "instructor group" that has attributes you wish to report on, you can create an Instructor Group Dimension which would then join to your fact table (and the bridge table would sit between the Group Dim and the Instructor Dim).

Naming convention: follow whatever you are using for existing tables: so if you name fact tables *_F and Dim tables *_D then call bridge tables *_B

Nick I have 2 keys in the bridge table.

1- Section Key
2- Instructor Key

Do I need to create a separate key called as Instructor Group Key in the bridge table.

Hunain

No - you need to have Section Key in your Fact table which you will join on