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?
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.