Databases Reference
In-Depth Information
FOR EXAMPLE
The Need for Distributed Operations
Let's revisit our nationwide auto parts chain store. Previously, it had set up the
local stores with the data each store needs for point-of-sale operations. Now
we're going to throw in another likely design requirement as a business rule.
Each customer must be listed once, and only once, in the consolidated database.
There are others requirements you might add, such as checking other
nearby locations if you don't have something in stock or letting customers
return items to any location, but this is enough for now. You can think about
how to handle those problems on your own, later.
Life has definitely gotten more complicated, possibly more complicated
than you realize at first glance. Each customer is tracked separately in the sys-
tem. You want to make sure that all sales are tracked for sales analysis and mar-
keting purposes (all this data feeds a data warehouse at the home office). Let's
say that you track customers by telephone number, and, by definition in your
data design document, each telephone number represents a unique customer.
The customer steps up to check out with a purchase, gives the employee
a telephone number, and the application does a search of the local customer
table. The number comes up not in file, so you need to enter a new customer
into the system, right? Not necessarily. What if this person purchased items
from a different location before? The application would do a second search,
as a distributed query, just to make sure. It could issue a query against all
of the other locations, but this is where the master copy comes in. The most
efficient way to check is to run a query against the master customer table
in the home office.
Now, let's assume that the customer was found in the master table. All
updates must be made to the local databases, so now you have another prob-
lem, updating the customer record with this sale. We mentioned embedding
the location as part of the primary key, and now that information becomes
important. The application would use that code location to identify the
remote database. It would have to create the order as a distributed transac-
tion that updates the local inventory, employee (for sales bonus), and order
tables while also updating a remote customer table.
Would it be easier to code the application so that updates that affect
other locations are made directly to the master table? Possibly, but this
would add two additional problems. One, the design requirement is that all
updates be made to the local database. The other is that you set up repli-
cation so that the remote copies update the master copy, not the other way
around. If you start allowing updates at both, the possibility of concurrency
errors (such as lost updates) and replication conflicts increases.
