1

I am new to data warehouse modelling so please bear with me.

In database warehouse modelling, the star schema is typically a fact table with multiple dimensions connected directly to it. However, what if there are two or more fact tables in the schema and some of the dimensions are related? Please see below as an example:

enter image description here

Is it allow to draw a line to relate the dimension tables in star schema database warehouse modelling? For example, assume dim_a is hotel_rooms and dim_d is customers, can I draw the line between the two table in the star schema since a hotel room has many customers staying?

In all the examples I searched online, the star schema is always connected to dimension tables and these tables are not drawn with a line to connect it.

Improve this question
8
  • There's already two many-to-many relationships between dim_a and dim_d in your diagram codified as fact_1 and fact_2. This would render any relationship directly from dim_a to dim_d suspect or the relationships to fact_1 and fact_2.
    – user212533
    May 6, 2021 at 17:45
  • If you were trying to associate customers to rooms, you usually would have a Booking or Occupancy fact table to express that relationship and necessary details.
    – user212533
    May 6, 2021 at 17:48
  • Hey there! Sorry I do not understand what you mean. What do you mean by suspect? I can't see the many-to-many relationship (the lines are basically one to many).
    – user3118602
    May 6, 2021 at 17:48
  • Suspect as in redundant or possibly breaking a business rule. If two entities have a one-to-many relationship to another entity, they have a many-to-many relationship through that entity (although it may be trivial or not make sense in a business context).
    – user212533
    May 6, 2021 at 17:52
  • I see. Assuming if fact_2 and dim_d are gone. Is dim_b and dim_a association allow in the star schema modelling?
    – user3118602
    May 6, 2021 at 17:58

1 Answer 1

Reset to default
2

Having multiple fact tables in a model is pretty common and is sometimes referred as a Fact Constellation. The shared dimensions are referred as Conformed Dimensions.

As @bbaird said in the comment there shouldn't be a direct one-to-many relationship between dim_a (hotel_rooms) and dim_d (customers) as this relationship should be modeled through a fact table (booking, reservation, stay, ...).

Relationships between dimensions are mainly used to drill-down from one level to another (for instance over a geographical area or as you modeled it for the time dimension). A model with dimensions linked like that is referred as a Snowflake. It is often also possible to flatten these hierarchical levels into a single dimension which might add complexity to maintain but provide better performance on some RDBMS.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.