I have heard textbook definitions of how to design a star schema regarding what goes in the fact table and what goes in the dimension tables, such as:
The fact table should contain core information about an object and dimensions should contain information about the facts
However, practically in business, I have seen a star schema designed where the fact table contains a surrogate key, a business key, and all single-valued fields of an object, and each dimension stores all the multi-valued fields of an object (hence the word dimension). For example, a person may be the object represented in a fact table. A person has one name, one age, etc., which all make viable facts in a fact table. A person may own multiple cars, each with their own attributes, which would represent a person's car dimension, stored as a dimension table with several columns to describe each car's attribute. In this example, this dimension table also includes a foreign key representing the business key of the corresponding row from the fact table.
So, if we can agree that this may be a suitable design, the problem that I am trying to overcome is how to do SCD type 2 (historical) on a multivalued dimension table. For my fact table full of single facts, it is obvious. I include two extra columns, an effective date and expiration date, and I use the business key to link common records where the most recent record has a
NULL expiration date, and all other historical records for the same business key have both an effective and expiration date indicating at what point in time they were the most current record.
How do I use this same concept on a dimension which represents a multivalued list? I essentially would like the same concept where I can (1) identify the current list (in this example, the cars a person owns) and (2) identify what the list was at any given moment in history. Can I just put an effective and expiration date on each dimension value? How then do I differentiate between values added after a certain time? Or deleted values?
But, if we do not agree on this design approach, please tell me what industry standard is so I can do this correctly.