Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Faculty Course Relationship

3 posters

Go down

Faculty Course Relationship Empty Faculty Course Relationship

Post  hunain Tue Feb 04, 2014 11:22 pm

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!

hunain

Posts : 19
Join date : 2013-09-15

Back to top Go down

Faculty Course Relationship Empty Re: Faculty Course Relationship

Post  ngalemmo Wed Feb 05, 2014 2:45 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Faculty Course Relationship Empty Re: Faculty Course Relationship

Post  nick_white Wed Feb 05, 2014 4:03 am

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

nick_white

Posts : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Faculty Course Relationship Empty Re: Faculty Course Relationship

Post  hunain Thu Feb 06, 2014 3:52 am

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.

hunain

Posts : 19
Join date : 2013-09-15

Back to top Go down

Faculty Course Relationship Empty Re: Faculty Course Relationship

Post  nick_white Thu Feb 06, 2014 6:11 am

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_white

Posts : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Faculty Course Relationship Empty Re: Faculty Course Relationship

Post  hunain Sat Feb 08, 2014 2:02 am

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

hunain

Posts : 19
Join date : 2013-09-15

Back to top Go down

Faculty Course Relationship Empty Re: Faculty Course Relationship

Post  nick_white Mon Feb 10, 2014 3:41 am

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

nick_white

Posts : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Faculty Course Relationship Empty Re: Faculty Course Relationship

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum