I'd just like to thank Joe for fighting the good fight. I'm seeing HORRIFIC assertions being posted by admittedly experienced folks.
Heck anybody trying to tell me they can't use computers to validate a credit card number (I mean EVERY VALIDATION THAT IS AVAILABLE ACCORDING TO THE SPEC), well I'd have to show them the door. Sorry, but it needs to be said. There is no excuse for this. I just hope I'm mistaken in how I'm reading such posts.
No one's ever said that. Like Joe, you're fighting a straw man. Of course you do any/all verifications needed for any cc (or other) value going into the db. But that doesn't affect the physical method I use internally to store that value. I'm surprised to see so many claims that it somehow should. Indeed, as with a date and a lot of other data types, the type itself helps verify the value.
If I store a cc as numeric, I don't have to valid that every char entered is numeric. I can CAST that numeric value to char or binary or any other format needed for validation. Why on earth should the validation process prevent me from saving bytes and processing overhead by storing the value as numeric rather than char??
Think back to what we do with cc's as an example.
We validate the number, then we authorize the account, then we authorize the transaction.
Why would we validate that the credit card number is all numeric without running the checksums? At the very time we're validating each digit, we could also be calculating the checksums. Why would we spread the validation across multiple systems. Its certainly possible that we could make this work, but we're talking about the very subjective matter about how we put together systems. I get it, this is not database administrator concerns, but its certainly on our todo list as computer professionals.
Its fine if you want to subsequently "compress" the data you are storing, but the data model isn't supposed to be concerned with that.
I get it, I could program everything with fortran and it will work fine, probably beat the snot out of SQL Server once I programmed the zillion lines needed to produce my own transactional storage that will use today's multiple cpu architectures. But that possibility is not what the relational model is about or even sensible application design for that matter.
Using a credit card number as a primary key would be a bad idea. For one thing there are PCI security implications of storing credit card numbers. One solution is to generate a GUID for each credit card and have only one table that stores the credit card number (encrypted) along with the GUID for lookup. So the primary key for this table would be the generated GUID which is not much different from an identity column.
Cc nums are not typical anyway, they'd almost certainly end up stored as varbinary because of the compression method(s) used on them.
Again, how you do the checking is irrelevant to the underlying storage format. That was my main point.
As to keys, agreed, you should certainly never use cc as a key, least of all because they also expire and thus change. But a bigint would be easier to deal with than a guid. Add a tinyint site origination code/value if you need to, to make the values guaranteed unique (or use the very high portions of the numeric values as a unique origination code).
Ok say I have a customer who uses a credit card to purchase, what do I send to the processor if not the card number? Now since I'm not personally responsible for any credit cards, tell me what key does the processor use to update the transaction that was generated and associated with the credit card? I don't personally do credit card processing or work at a bank, so for the bankers who post here, what do we actually do when processing these transactions?
edit: also, when I talk about Abraham Lincoln, the 16th president of the United States, that's enough to identify him. This didn't change once he "expired" so to speak LOL