Let me start by saying that I agree with most of the concerns expressed in this forum. I think that this is a technique that should be rarely used, and only in places where truly sparse data exists, not in the usage envisioned by the author.

One problem I see with this method is that the values in the lookup table are "shared by code" rather than by design. In other words, the user or application enters a text string for "address2" and the "instead of INSERT" trigger looks to see if the value already exists. If it does, the existing value is used.

Here's the problem:

Let's say that Joe lives in on 4th street in Seattle. He gets a record with a link to a string for the street address, rather than just storing the address. Now, Mary's record is entered. She lives on 4th street in Portland. Her record is entered and she get's a link to the same text string as Joe.

Now, Joe calls up and says that his address has to be modified. He lives on 4th Street NorthEast. The application updates the text string.

Now, Mary's address has changed. Possibly to an address that does not exist in her city.

I tried to make this discussion tangible to make it make sense, but my concern is that this would occur for a wide array of values.

I've worked with databases where the developers thought that they were being clever by looking EVERYTHING up, instead of just repeating information that should have been repeated. Maintaining the data was a NIGHTMARE.

I fear that wide use of this technique would lead to more databases with this problem.

As a result of this experience, I do not believe that this kind of "automatic lookup" is a valid exercise, and I certainly do not believe that it reduces complexity in the long run.