If you have meet me at a conference last year I am sure we have discussed my favorite topic: “Surrogate Keys”
Why is this so important to me?
The key message we have heard the last few years at every conference has been repeatable solutions, but if you want to create repeatable solutions it all starts with repeatable code.
So what has that to do with Surrogate Keys you might ask?
Well Surrogate Keys are one way (and until today one of the best I have found) to create functionality that is completely generic and reusable. Back a few years before I had even formalized my thoughts about this I called it “LEGO Programming” (okay I am Danish and love LEGO. Deal with it J) because I wanted to create code similar to these wonderful building blocks that can be placed anywhere in a construction and they just work.
LEGO is just one of these wonderful toys that you can be very creative with.
Why should my code be any different?
Well back to why Microsoft should look at this as a standard part of the platform:
- They want us to create generic solutions, so give us generic tools to do it with. Yes we can use things like RecordID, but a surrogate key is much more flexible and doesn’t depend on any record data. It also doesn’t suffer from renaming issues…
- My implementation has some faults. If you would use the INIT function it gets cleared out which is should not unless you actually insert a new record. This is standardly done twice in Table 37 and 39 as an example. We can work around it quite easy, but we should not have to do so. A proper implemented Surrogate Key in the platform would resolve that issue.
- Too many places in the application things have been done locally while using global wording causing things that I find highly regrettable. Take the table 5062 “Attachment”. Just based on the word you would assume it would work anywhere in the application and not just in a small local area of the functionality. Why should attachments be limited to one record when it could be so much more?
- A generic platform implementation of this would save the community a lot of work of adding Surrogate keys to all tables where it is needed.
- It is so simple to do compared to the benefit it would bring the community.
Just to be clear I am not pointing at anybody with my examples. They were developed in another age and great at the time. All I am trying to say is that we need to change gears.
It is time to start thinking generically and hold ourselves to a higher standard.