Databases Reference
In-Depth Information
To better understand what a circular reference is, let's look at the following
data model:
As you can see, a circular reference exists when the connections among the tables
simulate a circle and we have two routes to get from any given point to another. For
example, we can get from the
Main Data
table to the
Calendar
table either directly,
through the
YearMonth
route, or by going first to the
Promotions
table and then
moving to the
Calendar
table.
This is an issue that needs to be addressed, and one that can create severe data
inconsistency problems, performance degradation, and even crashes.
To solve the presented scenario, and based on how we deal with synthetic keys in
the flow chart, we should start by asking which of the created associations are
correct and which aren't. In this case, the association between the
Promotions
table
and the
Calendar
table is incorrect since the
Month
field stored in the former table
does not necessarily represent the month in which the promotion was used, but
rather the month in which the promotion was created. They just happen to have
the same name.
Most of the time, as in the earlier example, we will find that the problem arises from
unwanted associations and the issue is easy to solve (through the first method from
the section on synthetic keys). Other times, there are design challenges that need to
be analyzed thoroughly.
In
Chapter 8
,
Data modeling best practices
, there is a full section dedicated to address
the main design challenges a developer could come across when designing a
data model.
Now, let's quickly describe how the Table Viewer window works and how it can
become our best ally when data modeling.