Denormalize data or multiple-column key?

Question

I'm trying to make a judgment call in implementing a small-ish SQL Server '08 database.

I'm translating an output text file of a flat-file database from an old COBOL system to the aforementioned SQL Server database. It's a database of vehicle and real estate loans, which can be uniquely identified by the combination of a Lender ID (a seven-digit number), bank account number (15 digits), and "account suffix" (two digits).

I confess I'm pretty naive when it comes to database administration (to be honest, I've not really done it up until my current position), and I'm trying to determine which of two approaches are my best option for implementing a key which will index into several other tables:

1) Identify each loan using a three-column key of the above values, or
2) Denormalize the data by implementing a "key" column which is a 24-character string combining the three values.

The denormalization is ugly, granted, but I can't anticipate update anomalies occurring, since loans can't be passed back and forth between banks or change their loan suffix. A change in those values is guaranteed to be a different account.

A compound key is more elegant, but I've read a few treatises suggesting that it's a Bad Thing.

So, which option is likely to be a better choice, and more importantly, why?

Answer 1

I would suggest just using an auto-incrementing numeric surrogate key. Why would it need to be a mashup of the other three "key" columns?

Answer 2

If this is reference data that won't be updated often, then using the multi-part key should be fine.

If this is high-traffic transactional data, then add a surrogate key (int identity, clustered primary key) and make the three-part key an alternate key.

I would not suggest implementing option 2 at all.

Answer 3

I would use an autogenerated surrogate key and then put a unique index on the natural key. This way if the natural key changes (and it might if say a a bank got bought out by another bank), then it only needs to change in one place. The most importatnt thing in using a surrogate key is to ensure uniqueness of the natural key if one exiusts and the unique index will do that.