Your example is about normalization. A company code should be part of the company record in the company table. If you store that code again in the region_office table you do not have normalized data. If you have twenty tables that use the company code, you have twenty things that are going to break when the use decides to change the company code system. If you are using the company_pk as the company_fk in all the other tables, he can change the data he can see ( company code) to anything he wants in one place - the company table - and every region_office screen etc will show the correct company code.



The joins you need in meantime are annoying, sure, but it really is better design. In SF you can make pulling those related values (company_code) part of the SQL select or sproc that gets the data and then use a custom field property to give you a strongly typed property.

Ah, I understand now.

yes, of course, you need a JOIN ; and you think that all these necessary joins will affect performances, and increase complexity in supporting.

But in your example, what happens when data is modified in natural key 'CompanyCode' of parent table 'Companies'. You need to update all child records in table 'Employees', and you probably have coded a trigger on that purpose, or even better, an constraint with on update cascade (trigger is procedural, and slower than declarative constraint).

With a surrogate key, you will never update child tables, but only one record in parent table . Keep in mind that writing on a physical disk (what you need with an update) is always slower than reading from memory (and your view will be in a cache plan in memory, if you have good indexes)

And the more you have foreign keys in the child table, the more performances are increased with surrogate keys.

Additional maintenance effort in preparing views? not sure. IMHO, less effort in development (both on server side and client side) and in maintenance.
as an example, let's say tomorrow you need to get company name with employee (new requirement of your main customer ). You will need a view, ok? So, why not having this view today, and simply add a field from 'companies' table in it. Views bring you scalability.

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.

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,

Thank you for valuable comments.

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

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

Good comments, Charles!

So no strong opinion either way, Charles?

________________
_____/ Regards,
____/ al

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 !

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.