Microsoft Office Tutorials and References
In Depth Information
A big issue with this solution is that you need to define a column for each courier. This works
fine when you need to analyze a small number of couriers, but if you need to perform analysis
with a variable number of couriers or a very high number of them, you might encounter some
usability problems. So because we are striving for perfection, we can study a different data
model that solves this problem, too.
Using Many-to-Many Relationships
A very interesting solution to the same scenario, which solves the problem of hardcoding the
courier name in the formula, is to use many-to-many relationships, which we introduced in
Chapter 4, “Data Models.” This last solution, although very elegant and powerful, is complex.
Before we describe the solution, let us briefly review the problem from the data model point of
view. In Figure 10-28, you can see a simplified version of the tables we have been using up to
now so that you can better understand the data model. Please note that, for the price list, we
are using an expanded version of the price list, wherein we removed the min and max weights
and expanded them to the granularity level, as we previously did with the banding example.
FIguRE 10-28 The first data model for the courier simulation.
You have only two tables:
■ Orders Contains the order with all its relevant information. In the diagram, we show
only country and weight, which are the useful information for freight computation.
■ PriceList Contains the price that each courier applies for a specific weight shipped to
a specific country. This table is slightly different from the one we used before (in which
we had a range of weights), but the information content is basically the same.
From the data model point of view, you would like to create a relationship between the two
tables using the columns country and weight. Unfortunately, this cannot be done because
country and weight are not unique keys for the tables: they are repeated both in Orders and
PriceList, so you cannot create a standard relationship between them. The reason country
and weight are repeated is that they appear once for each courier. In other words, a single
order refers to many price list rows and the contrary happens also: a single price list applies
to many orders.