0

I have a Source Table which looks like as below (pls focus on the show_id column) [source table1

And below is the Dim and Fact Table Diagram from my school's guide: dim and fact diagram

They put the show_id from the Source Table directly inside the Fact table, which confuses me. I think that's not what I learned. Now I'm wondering whether should I put the show_id inside the Dim_info table instead or just don't include the show_id at all

Any advice?

Hoang Minh Quang FX15045
  • 733
  • 1
  • 4
  • 15
  • 1
    I would agree that is doesn’t look right. I would have had a DIM_SHOW table, rather than a DIM_INFO, and with only one of those 2 ids in the fact table – NickW Nov 06 '22 at 20:24

1 Answers1

1

There are two problems here.

  1. DIM_INFO is a vague table. If it's storing information about shows, then it should probably be called DIM_SHOW. My general rule of thumb here is that I should be able to ask ten knowledgeable people in the business what the "info" dimension is ... and if all ten can't give me a cogent answer, then a more descriptive table name is needed. Ergo, the suggestion about DIM_SHOW.

  2. Show_ID appears to be a natural key from whatever the source system is for the show data. Natural keys should never be used to join facts to dimensions, as they are managed externally and are considered unreliable. DIM_SHOW should have its own surrogate key (ShowID?), which would be generated by the ETL process that loads the table. That surrogate key would then be used in the fact table.

BayouKid
  • 69
  • 4