| Referential Integrity | KEVIN RESCHENBERG
12-21-2005 |
When old records are converted into a new PeopleSoft system,
the team usually plans to populate tables in the proper
order, even when data is inserted into tables directly using
SQR or SQL. Many people assume that the database will enforce
this order through referential integrity checks and may be
surprised to learn that PeopleSoft doesn't use database
referential integrity. Instead, it tries to enforce the
rules itself through PeopleCode and the component
processor.
What is referential integrity? Let's consider a simple example.
Suppose that you are selling items and have an ORDER table and a
CUSTOMER table. Each customer may have 0 or many orders.
The ORDER table contains a customer ID field linking it to
the CUSTOMER table. Referential integrity rules say three
things: First, you should not be able to put an invalid
customer ID on the ORDER table. Another way of saying this
is that you cannot create an order unless you already have
a customer for that order. Second, if the customer ID
changes—XYZ Company's ID changes from XYZ to 0001—then
all associated orders must be changed so that the customer ID
field contains 0001. Third, if a customer's row is deleted,
all of the linked orders should be deleted at the same time.
These updates can be performed automatically
by the database. This ensures that you will never have
inconsistent data—an
order without a corresponding customer. They are called
"cascading updates" and "cascading deletes" and
can continue on to other tables, such as an ORDER_LINE
table. Deleting a customer row deletes the orders, which
then causes the order lines to be deleted. It sounds like a
good idea. Why doesn't PeopleSoft take advantage
of this feature?
There are probably many reasons, but I'll guess at a few
of them. First, it wouldn't prevent certain types of
referential integrity problems, especially concerning
effective-dated rows. I'll talk about that in the
context of component-level processing in next week's
post. Second, PeopleSoft runs on many different database
systems, and it may be difficult (though I don't know this
for sure) to specify the same constraints across all
platforms. Third, it's rarely necessary. Since a user
generally can't change a level 0 key or delete a level 0
row, the situation doesn't come up frequently. Fourth,
there must be some performance implications to having
the database do these checks. Finally, there could be
dangers lurking in the system if we relied on the
RDBMS to enforce referential integrity.
Consider this scenario. In the HRMS system,
the JOB table links each
employee to his or her supervisor through the
SUPERVISOR_ID field. Suppose that you added a
custom field to PERSONAL_DATA or PERSON
and stored it there instead, so that an employee's
supervisor's ID would be more readily available.
Then you told your database to
enforce referential integrity. What could happen?
Suppose that your company decides to hire a new CEO and
offers him the standard $10 million package. The HR
department enters his information and then links
all of the executive vice presidents' records to
his, so that the new CEO is the new supervisor for each EVP.
So far, so good. But then just before the new
CEO is supposed to arrive, he decides to take your
competitor's $20 million offer instead. The HR
department uses the ID Delete function to remove his
records. (Many companies do this for no-shows.
Whether that's a good policy, I don't know.)
Now that the new person's records have been deleted,
all of the EVP's supervisor IDs are invalid. Therefore,
the database deletes their personal information rows. The delete then
cascades to the Senior VPs, the VPs, the Directors,
and (10 levels later) to us. At the same time
it takes out all employee-related information
throughout the system. You're left with a database
as clean as the day it was installed. Don't expect a
paycheck this week!
A week or so ago I read an online analysis dismissing
the major ERP systems as
"all the same," old and flawed. A lack of
database-enforced referential integrity was one
example cited by the writer. I can't find it now,
but the whole thing was a
bit over the top. As usual,
there may be valid reasons for a design decision
that looks wrong at first glance.
|
