Re: data modeling confessions, part 2
Date: Wed, 21 Oct 2009 11:08:39 -0700 (PDT)
> Since our system is supposed to have something to do with master data,
> it of course additionally generates its own, authoritative, master ID
> for each new object.
As an addendum, I just learned today that one of our affiliate systems contains a nifty feature: they can manually change this authoritative, master ID. And no, updates to our database are not correlated by the ID we assigned. Instead they're done via some fuzzy logic, among other things involving the surrogate the source system is using. The result? We have duplicate, authoritative master ID's now.
That problem is not a new one. A thorough cleansing exercise already took place before my "reign" to eradicate it. It's just that nobody thought of declaring the problematic attribute UNIQUE after the cleasing was done, and the problem recurred. (It couldn't have been declared the primary key since, after all, we're working on top of Siebel's out-of-the-box data model which elects to place objects there which cannot be assigned one of our ID's. Plus of course the primary key is already set as the Siebel internal surrogate.)
On the plus side, I was assured the fix to stop this from happening again was coming Any Day Now. It essentially consists of keeping the matching logic the same, but ignoring updates to the master ID. Correlating updates by our ID was apparently not an option, eventhough one of my colleagues has been talking about that ever since the cleanup took place.
As the perennial optimist, I'm hoping the new code rejects updates with changed master ID's or actively reverts them. But at this point I think it's more likely that the update will be accepted, only with the master ID stripped off, giving rise to inconsistency between the two communicating systems.
The lesson? If you couple loosely, you don't need yet another key but simply a rock-solid mapping mechanism. If you try to do MDM, you should have the authority to force your affiliates to adopt your master key as the One True Key. Mixing the two approaches ain't really an option.
-- SampoReceived on Wed Oct 21 2009 - 20:08:39 CEST