jophuh
jophuh

Reputation: 317

This Star Schema is Exactly Backwards, how do I correct it?

This is the table I would normally use as the center fact table for a star schema data model. This shows each event I want to consider, which is the last time a restaurant was audited, and the next time it needs to be audited.

LastRestaurantAudit
===================
RestaurantID
LastAuditID
LastAuditDate
NextScheduledAudit

I want to combine it with these dimensional tables:

RestaurantInfo
==============
RestaurantID
RestaurantName
RestaurantCategory
RestaurantLocation
RestaurantContracting
=====================
RestaurantID
ContractID
ContractName
ContractType

When I do this my star schema ends up backwards. My central table isn't the many table and my outer tables aren't the 1 tables. This is because

  1. RestaurantInfo: Each RestaurantID can have multiple RestaurantCategory
  2. LastRestaurantAudit: Each RestaurantID only has one LastAuditID, one LastAuditDate, and one NextScheduledAudit
  3. RestaurantContracting Each RestaurantID can have multiple ContractType
RestaurantInfo        LastRestaurantAudit  RestaurantContracting
==============        ===================  =====================
RestaurantID(*)-------(1)RestaurantID(1)---(*)RestaurantID
RestaurantName        LastAuditID          ContractID
RestaurantCategory    LastAuditDate        ContractName
RestaurantLocation    NextScheduledAudit   ContractType

How do I reconfigure to get a long many table in the center and wide 1 tables on either end? I've got it completely backwards from what I've read Kimball recommends.

Upvotes: -2

Views: 69

Answers (0)

Related Questions