I have created a Hub called Ward. The unique key is the Ward ID and Start date. This is because the same Ward can appear more than once in the source table with a different name (because the ward’s purpose can change). E.g. Between Jan 2023 - Dec 2023, Ward ID 001 has been used for General Medicine but, since 2024 Jan, the same ward has been used for Cardiology.
But while building the Link table, I faced a problem, in the source transaction table, though there is a ward ID, there is no start date, and because of that my LNK and HUB cannot be joined.
Is the way around this to create the Ward hub only with distinct Ward IDs and then add the SAT as a dependent key relationship?
A ward it’s a physical room independent of date.
It’s purpose can be modeled via effective SAT either on hub itself with a category code, but I suppose the medicine types deserve themselves it’s own hub so create a 2nd hub for the semantics (warden_type) then you attach the effective SAT to link_warden_semantics.
Probably it’s all boils down to:
Simple few attributes then hub SAT is enough with a Dependent child key hub pk(hub_bk, warden_type)
Many attributes or need of further relations (most probable) then 2nd hub for the all specific medicine semantics which is independent of where physically it’s currently being delivered.
Do not use dates as part of BK - I agree with this.
My solution:
U should have HUB_WARD with BK: Ward-Id & attributes (Ward dimensions, Date-built/etc) in corresponding SAT_WARD.
Capture the Ward’s purpose or category in another HUB_WARD_CATEGORY
BK: Category-Id with its SAT containing Descriptions (“General Medicine”/etc)
Then Link the two Hub’s with the LINKSAT providing the Date-Ward-repurposed. The latest of these dates will give U the current purpose of a specific Ward.