Databases Reference
In-Depth Information
We are using the ampersand operator to merge the values from the two fields into
one. We then assign an alias to the new calculated field by using the
as
keyword.
Adding a delimiter to concatenated fields
It's always a good idea to add a separator between fields when
concatenating them to ensure data consistency. For example:
Year & ' | ' & Month
as YearMonth
The resulting data model, created using compound keys, is shown in the
following screenshot:
The synthetic key has been successfully eliminated from the data model and the
associations between both tables have been explicitly defined.
Dealing with circular references
Similar to how synthetic keys are created, a circular reference can also be the result
of unwanted associations in our data model and, as such, they can be fixed using the
same principles described earlier. But before getting into how to solve them, let's first
see what they are.
We can think of the data model as a map that shows the paths through which we
can walk to get from one point to another. For instance, considering the data model
seen in the previous section, the only path to get from the fact table to the
Calendar
table is the
YearMonth
route. This means in order to get there you must know what
Month
and
Year
you are looking for in the
Calendar
table.
However, when the data model becomes more and more complex, with a larger
number of tables, and more and more destination points, we might also get to a point
where we have more than one route connecting point A to point B. All roads lead to
Rome, they say. Well, in our case, we must always have one road between any two
points. Otherwise, we would be having a circular reference.
