On 22 Oct 2004, ed.prochak_at_magicinterface.com wrote:
> Galen Boyer <galenboyer_at_hotpop.com> wrote in message
> news:<uy8hz3kmb.fsf_at_standardandpoors.com>...
>> On 21 Oct 2004, ed.prochak_at_magicinterface.com wrote:
>> > wizofoz2k_at_yahoo.com.au (Noons) wrote in message
>> >> There is no such criticism. No one can expect a prescient
>> >> knowledge about a complex system of interactions. The
>> >> maths alone to allow for that, would make most systems not
>> >> viable economically. What is expected however is a
>> >> flexible design that can be adapted with minimal impact to
>> >> new discoveries about that system. And surrogate keys lend
>> >> themselves perfectly to that.
>> >
>> > Can you give an example of how you handled this? How does
>> > Surrogate keys fit in a flexible design in a greater way
>> > than Natural keys? How about this for raw, very high level
>> > entities: Customer, PhoneNumber, Address. If you were
>> > working on a billing system DB for the phone company how
>> > might you make a model that allowed change while using
>> > surrogate keys? How would an equivalent natural key model
>> > not be as flexible?
>>
>> Here's my stab,
>>
>> Basically, surrogate keys allow you to model the business and
>> start implementation without fully knowing the actual natural
>> keys of the source data. For example, we have a system that
>> has to model a loan (among other things). For one grouping of
>> customers (defined by the market they are supporting), the
>> loan has a natural key, deal/collateral_id while for another
>> grouping of customers, the natural key is their internal id
>> for the loan. Our database stores the data for both customers
>> unifying it for presentation back to them. Both groups of
>> customers want our analytic engine but they have different
>> ways to key their data. Plus, we have a third product that is
>> on the horizon which will have an unknown set of data files,
>> but they will also interact with loans, plus, we will have
>> loan rows created directly by our application.
>>
>> Its easy to model the business and say that each loan has an
>> id and that a loan_id is related to a property through our
>> property_loan table. We immediately where able to design
>> that, and then we were able to immediately start building our
>> application.
>>
>> What I then did was had a schema for translating the "natural
>> key" to our internal key. Then, as new "natural keys" show
>> up, we code that specific layer. The end result is that we
>> have one surrogate key for the application but we can always
>> get at its natural key.
>
> So how did you normalize? Sounds like you couldn't and didn't.
Our loan table has a loan_id.
> But a Loan ID sounds a lot like a natural key in this
> context.
Nope. There were multiple natural keys, each from different
sources.
> Much like a work order number. You expose it to users and let
> the system fly.
Based on the market, we would subtype the loan table and give out
something like, cmbs_loan, and on that loan table we would put
the "natural key" for searching and uniqueness.
> That isn't a surrogate key any longer, as far as I can
> tell. End Users never see the surogate keys.
>
> Also, this doesn't sound flexible at all. If there are three
> loan type, you are building three loan applications.
We have one application, multiple subtypes.
> The fact that all three types are even stored in the same
> database is coincidental. When you add loan type 4, what if any
> of the previous code can you use?
>
>
> Sorry, Galen. It's an interesting approach, but an
> underwhelming example.
--
Galen Boyer
Received on Mon Oct 25 2004 - 07:51:17 CDT
