In a star schema, can a dimension table such as customer_dim have a column that references another dimension?
customer_id first_name last_name email primary_billing_address_dim
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
When dimensions have references to other dimensions, we call that a Snow Flake instead of a Star schema. Depending on how you query your model later on, one approach might be more efficient than the other. It is possible to transform a Snow Flake in a Star Schema by flattening everything in the dimension linked to the fact table(s) – customer_dim in your example.
Some tools (like Microstrategy) behaves very well with a Snow Flake model. On the contrary Oracle Business Intelligence Enterprise Edition will require you to model your business model as a star schema.
For ad-hoc query performance, both approach should be benchmarked. A pure star schema reduces the number of joins so it might be faster. In term of data quality and master data management, the snow flake is probably easier to handle.