Hi,

Thank you for all the informative replies.

As we all understand the pros / cons of natural/surrogate key, and ALL of us prefer surrogate rather than natural key.

I am very interested to know, how you all overcome the limitation of surrogate key?



For example, :



1. Too many join, difficult to optimize index especially using reporting tool like foxfire / stonefieldQuery which report is user defined, filter condition is so vary.



2. Troublesome for support team/developer to do troubleshooting as we always need to check from master table first, get the surrogate key then only able to query the rest. It complicated the troubleshooting process. Even though standard view/stored proc can be created (which join all FK table), but extra effort required.



3. We have our own generic Import module, which allow customer to prepare data in excel, defined corresponding schema in our app and import the excel data. For sure, customer will not care about the surrogate key; data is in natural key way. We still need to have additional effort lookup the corresponding surrogate from master table using natural key. It seem like defeat the purpose of surrogate key.



4. We have a lot processing, which again, still need natural key for lookup and searching. It also seem like defeat the purpose of surrogate key.



Based on the example I posted above, do you think surrogate key is suitable in our environment? What is your approach on these?



What lead you to decide to use surrogate?



Thank you for efforts on this sharing.

Hi Chan,



I guess the bottom line is that you use whatever is appropriate for your app. If your data and your requirements make natural keys the way to go then that's the way to go.



Cheers, Peter

I offer my .02 for free :



Never ever ever ever key on data. Data is something the user expects to be able to change without regard for under-the-hood consequences. In 25 years I have heard at least 100 variations on the theme of "Our part numbers/student id numbers/field office names will never change".



Surrogates are single fields that uniquely identify a row and if every other value in that row changes, it will still be the same row. The users don't even know they are there.



I have never seen anyone sorry they have used surrogate keys. I have been involved in many many situations in either mentoring or project rescue where there was a great deal of regret about not using them.

In my hurry to rant on a favorite topic I ignored your actual questions



1. Too many join, difficult to optimize index especially using reporting tool like foxfire / stonefieldQuery which report is user defined, filter condition is so vary.



This is more an issue of normalization than what kind of keys you use. The number of joins is determined by the normalization, not the keys that join the tables.



2. Troublesome for support team/developer to do troubleshooting as we always need to check from master table first, get the surrogate key then only able to query the rest. It complicated the troubleshooting process. Even though standard view/stored proc can be created (which join all FK table), but extra effort required.



This again is an issue of normalization, not the type of PK, FK that are used. If you are talking about keying on actual redundant data between tables, you are compounding the problem of what happens if the user decides they are going to start using a different part number system etc.



3. We have our own generic Import module, which allow customer to prepare data in excel, defined corresponding schema in our app and import the excel data. For sure, customer will not care about the surrogate key; data is in natural key way. We still need to have additional effort lookup the corresponding surrogate from master table using natural key. It seem like defeat the purpose of surrogate key.



Your import module must be validating the customer's data before putting it into the app. You assign your keys there. Of course you need to use the natural keys, but you can have indexes other than PK indexes. Nothing wrong with having candidate keys that perform a similar function and give performance benefits for particular tasks.



4. We have a lot processing, which again, still need natural key for lookup and searching. It also seem like defeat the purpose of surrogate key.



Doesn't defeat the purpose of the surrogate key - which is to connect normalized tables and identify a row for inserts and updates. You can have other indexes to increase performance for lookups and searching by the user.



Based on the example I posted above, do you think surrogate key is suitable in our environment?



Absolutely !

So no strong opinion either way, Charles?

________________
_____/ Regards,
____/ al

Good comments, Charles!

I do feel a little less strongly than my belief that Buffy the Vampire Slayer is one of the greatest TV shows of all time

Hi,

Thank you for valuable comments.

Before anything, I would like to ask, Are Department Code, Company Code surrogate key or natural key?

Hi Chankk,

if a key means anything, it's a natural key. The only signification of a surrogate key must be the ability to identify the row, not the data in the row.

so, if CompanyName='Microfour', the surrogate key would be an integer (or a GUI if you need replication), even if CompanyCode ='Micro4' or CompanyCode='SFteam' or CompanyCode= whatever you change

Hi,

If companyCode is natural key, then why surrogate key not need more join? For example, if I have table as below:







As example above, surrogate key approach required JOIN, however natural key doesn't. This is why we think it required more JOIN.



Of course, we can use drop down control, specify CompanyId as ValueField, CompanyCode as DidsplayField to let user to choose company code. However, what about support team? Preparing view for them is additional maintenance effort.



Thank you for sharing.