Multiple Dimension Tables linked to Fact table using same key field
Question on data model design.
I have three tables. Fact table being production events. Two dimension tables - Job_Master that has one record per Job and fields such as Plant, Ordered Qty, Due Date etc. Job_Customer is a list of customers that have used product from that Job. There can be multiple customers per Job. The three tables all link with a key field %Job.
I choose to have both dimension tables connect using %Job field to Fact rather than using a snowflake schema of linking the Fact to Job Master and then Job Master to Job_Customer. I can't combine them to one dimension table as that would replicate the Job_Master details for each customer.
This works, but am curious if there are any known downsides, particularly in performance for front-end calculation. Should I be avoiding this design and instead using snowflake, or possible two separate key fields to join the tables.
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.