Surrogate keys v/s natural keys
I am currently working on a database design problem. I work in the financial domain and I deal with data related to benchmarks and securities. The problem with such databases are that each security has multiple identifiers in the real world. They could be Uniquely identified by a SEDOL, CUSIP or ISIN or the many other type of identification standards available. This lead me to the question of designing the tables using a surrogate keys but I was still not sure how my team would react as they had already design loads based on real world identifiers and named it security_id. Unfortunately security_id could be a SEDOL, a CUSIP or ISIN or whatever depending on what region or benchmark the security belongs to. That isn’t really a reliable identifier. And hence to avoid annoying the guy in my team who does data loading I redesigned my queries to do some intelligent identifier resolutions. This cost us a lot. Queries that would perform in barely 500ms now take about 1500ms. ...