Ok I back with Lookup Lists and Surrogate Key vs. Natural Key, this time I have a complex scenario.

We have 3 dropdown lists on a screen; they represent a medical condition for instance a heart attack:

Underlying Cause
Treatment
Initial Outcome

Underlying Cause would be a list of all possible conditions that could cause the heart attack.

Treatment items would depend on Underlying Cause and would be an empty list until a selection is made in Underlying Cause which would fire off the list being populated.

Initial Outcome would also be dependent on the Treatment.

Also this screen would be installed at many different sites and each site may have different descriptions for the items in the lists although they would have the same meaning, they may even be in a different language.

So I couldn't rely on the descriptions to code the logic. I would use a surrogate key to define the logic.

When I save the record should I store the surrogate key or the description?

My thoughts are I should store the surrogate key.

I would like yours thoughts on this, particularly from the Natural Key advocates and how they would address this scenario.

Thanks

Richard

store the surrogate key

You were gone?

In healthcare systems I have worked with there are organisational and national standards that mandate standard diagnosis, treatment codes and so forth. There are also international standards for those things (ICD, SNOMED).

I'm very surprised if your organisation isn't already using such standards. If they are then they already have a suitable key - the diagnosis or treatment codes specified in the standard.

Yes we do use those codes. We also use other codes. We have to record items that we expect to become coded items in the future. Also some of the systems we have to interface to have multiple definition's of national codes (ethnic origin is one) so using a national code would be in-practical. We generally have our own internal keys and have corresponding entries in the look-up table to national and local definitions.

The problem i have is we have a new director advocating using natural keys he does not have a healthcare background. So far i am convinced a surrogate key is the best method for our business model. I am interested to see if natural keys could provide a viable solution.

I still don't see any problem with using an official code. You can choose the most useful or comprehensive of the standards and stick to those codes throughout for your foreign keys.

You can still hold the mapping to the other alternative coding schemes in separate tables. You only have to store that once and presumably you would have to do the same if you used a surrogate anyway.

Why do that rather than use a surrogate? Well, you've already suggested one reason why. You need to create codes for things that don't yet exist in the standard list. That means you'll need new lists of codes that the users can understand and enter into the system (a surrogate alone won't do of course because users never see it). You'll want a central authority to be responsible for creating those codes and they will use some kind of coding scheme to make sure they don't use the same code twice.

The modern buzzword for this process is Master Data Management (we used to call it just plain Data Management!). In other words you will need more than just a surrogate - you need a code with a single meaning defined by some central authority and understood by all your users, AKA a business key or natural key.