What is the general practice when it comes to choosing a unique identifier? In my case I have the option to use the eBay unique listing ID (Item ID) or my own internal ID for use in table joins and general identification. The argument could be made that some 3rd party identifiers are non-numeric and I would understand this proposition but in my specific case the identifier is numeric. Thanks for any advice or personal experiences in advance!
Add a comment
|
2 Answers
To be safe you'll want both. Internally you should create and use your own unique IDs to maintain a clean relational model with FKs between your tables while keeping a reference to the eBay unique ID in an appropriate table. If you need to reference something by the eBay ID you will have it available, but should the eBay unique ID rules change it does not affect the consistency of your existing table relations and architecture.
Your question has no right answer, and consequently stands to elicit many opinions. Mine is foursquare in favor of using natural keys unless and until they become unwieldy. My mantra is, store what you know.
The first reason to favor natural keys is that they force you to consider the table design in terms of identifying characteristics. You're much more likely to develop a normalized design if you're grouping attributes into tables by cardinality, and 3NF is your friend.
The second reason to favor natural keys is that you preserve the information provided from the real world -- the "enterprise of interest" -- in the database. If the key is an internally generated number, you risk disassociating the data with its real-world identifier.
The third reason is simplicity: internal identifiers add no information. They add data, but they record no new information about the world being modeled.
Of the many thousands of tables I've designed, those relying on a synthetic key are just a handful, perhaps 0.1%. I'll offer you an example of a case where it was necessary, and still could have been done better.
Stocks (and other instruments) on the stock market have no unique identifier. Each exchange uses its own ticker; various bodies assign identifiers. Several "identifiers" may therefore designate the same security. Over time, some identifiers are reused to represent different securities. (Consider what a 180-day future means as time progresses.) Firms dealing in securities have the need to recognize securities regardless of the externally provided identifier. The information to map from one identifier to another is a closely guarded trade secret, ironically much too valuable to be freely traded.
For example, IBM's stock is known as "IBM" on the NYSE, and to the Committee on Uniform Security Identification Procedures as #459200101. The two organizations share no common designator for it. In fact there are about 7 potential bodies to assign the identifier, depending on the domain.
A purely theoretical approach to capture that relationship would keep all the identifier pairs in the matrix for each instrument. I doubt anyone's ever done that in a production system. Instead, you design a table {Id, Identifier, Type, StartDate}, for which Id is an internally generated value that is common for all Identifiers for one instrument. The matrix of combinations can then be generated by joining the table to itself.
That was the necessary part. We needed something to associate different identifiers. Because none existed, we generated one. And all was good.
Then we took the next, seeming logical, step. Possessed of a unique identifier of our own making, we could use it to identify instruments throughout the database. Voila! No more grotty, incompatible not-quite-unique externally provided identifiers. Just good, clean numbers!
Three bad things came of that decision.
Sometimes the process of determining what maps to what produced results that later proved to be wrong. Either two identifiers were not related as had been previously asserted, or two seemingly distinct instruments were in fact one and the same. It therefore became necessary to change the mapping, and to propagate that change across the dozen or so tables that referenced it. And, later, to the dozen or so other systems that used it.
Using our internal identifier "canonically" broke the association with the external identifier used when the information was provided by the external data source. We knew the instrument, yes, but not the identifier by which it was known to a particular vendor. That made reconciliation with external sources more difficult.
The problem of change over time eventually meant the internal identifier became undependable as an intersystem link. If you didn't know when the identifier was applied, you couldn't know to which instrument it really referred. Instead of clarifying, the internal identifier joined the technical debt, a source of error and unnecessary complexity.
How could the downside have been avoided? At the cost of some query complexity, always and only storing instrument data by its externally supplied identifier. Using the cross-reference matrix when necessary, and never exposing the internal identifier. In retrospect it's impossible to come to any other conclusion. Using the natural keys would have preserved information and prevented confusion & error. Store what you know.
In my experience, the lesson is learnable, but unteachable. Twice I've had the chance to explain it to someone newly confronting the same problem. We need a unique identifier! Both times the smart person listening to me decided my lessons didn't apply to his situation; he would invent the truly unique security identifier. Hubris is a self-renewing resource.
What should you do? Database design choices, good and bad, are magnified over space and time. The larger the database -- rows and columns -- and the more the information is dispersed, and the longer it's around, the greater the payback or pain. Whether or not my lesson applies to your situation depends in part on how important your database is or will be. Naturally that's up to you to decide.
