Avatar of Coco Beans
Coco Beans
 asked on

FACT TABLE updated

Hi,

I have an issue with my FACTS. I'm not sure what I should do with it.  

I have an registration fact table.  A student is registered in a College in January.  At the time of registration, the College is a public College.  Every February the colleges can be given a private status, and any registration after February will be at a 'Private College'.

When I try to analyse the number of students in a private college.  Anyone registered in January is public but after January is Private.  Once the college turns private, I would like to the registration to be classed as private.

I was thinking of adding a flag to my FACT private/Public.  And updating the Flag retrospectively if the College changes status.


Any thoughts?


Thanks for any help!
DatabasesSQL* data modelling

Avatar of undefined
Last Comment
Coco Beans

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
als315

May be this flag shoud be in table with College properties?
Qlemo

Simce you have to check against FACTS, no. If the flag had effect on all registrations, that would work, but here you need more than just a single global flag.
ste5an

hmm, the registration is the fact table as you said. Then the college is a dimension. According to your description a SCD-2 (slowly chaninging dimension, type 2).
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Qlemo

That's oversized, ste5an, and against KISS. You do not have to document in each record why the status has been changed, because there is a single and simple event changing a lot of records.
SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Coco Beans

ASKER
Thank you