Database Reference
In-Depth Information
After applying the consolidation above and physical naming standards to the model, we
have this physical data model:
Notice there are different ways of combining the entities. Normally when combining, we
collapse the entity on the one side (the parent entity) into the entity on the many side (the
child). For example, we collapsed
Survey Respondent
into
Completed Survey
,
Possible
Answer
into
Survey Question Possible Answer
, and
Survey Category
into
Survey
.
We can also roll up the child entity into the parent entity, as we did with the subtypes as
well as with
Survey Section
. We needed to add a
Survey Type Code
which has values
P
for
Pending Survey
,
A
for
Active Survey
, and
R
for
Retired Survey
.
We rolled
Industry
down into
Organization
, forcing us to come up with a maximum
number of
Industries
an
Organization
can belong to (in this case three). In MongoDB
however, due it its flexible structure, we can have any number of industries in the
Organ-
ization
collection by using an array.
STEP 2: ACCOMMODATE HISTORY
Recall there are four types of Slowly Changing Dimensions (SCDs for short). An SCD
of Type 0 means we are only storing the original state and not storing changes. A Type 1
means the most current view. A Type 2 means we are storing all possible changes to the
data in a collection—a complete history whenever anything changes. Type 3 means we
have a requirement for some history—for example, the most current view and the previous
view or the most current view and the original view.
We complete the following Collection History Template for our Survey model:
Collection History Template