>>Yes, in this example you can use an integer field, but you are sidestepping my point: what happens when you have character PKs and integer PKs?
>
>Basicly nothing. The point you've got to watch out for that you don't have programmed modules that expects integer PKs. Since I don't like to be forced into surrogate keys, I dont have got such limitations in my framework.

You still have to code differently in your middle tier. I think this case was brought up by Erik Moore. You prefer this method, I do not.

>>It doesn't give me a lot of faith in the rest of what the author writes.
>
>Bad, bad argument. You as a developer should be able to filter out what's right and what's wrong. If you make your decisions based on feelings, a less good example, you might miss some things that make very good sense at all. Just rear the whole article and analyze it piece by piece.

No, good argument. It the author cannot even see this, why should I have faith in the rest of his arguments? This decision was not based on feelings, but facts: his solution has a potentially serious flaw, one he either isn't aware of or chose not to point out. Either way, not good. So why am I suppose to believe the rest of it?

>AFAIK, the cities in our country are pretty static. Our country is pretty crowded and there simply is not enough room for more cities. I can't recall a new city having arised after Almere or Lelystad (and thats about a 25 years or more ago).
>
>But this is not an issue either. As long as it is possible to extend this lookup table, there is nothing wrong.

Walter, the cities example above was your scenario, not mine.

"I don't like to spend hours to adapt the program to a new situation when not necessary at all."

This is your scenario, but you have not provided an advantage to using intelligent keys.

>As I said those strategies are different, the lookup table contains all known cities, so even if you start with an empty adress table you're ensured that the city entered does exists, With the DISCTINCT approach you're able to insert incorrect spelled cities and will be ask for confimation each time a city was entered for the first time. The lookup table can be applied in more than one table (for consistency ??) without having a seperate DISTINCT solutions for each new one. IOW, the lookup table IMO is a far better solution anyways.

In your original scenario, the Address table already existed and had data, and the request from the manager came later. Now, your are starting with an empty database. Which is it?

>In our country we could use the postal code table maintained by our national PTT. This one is updated regulary (So you're ensured that all new postalcodes are included). Our PTT even offers a solution to check if a certain adress exists.

You failed to mention this in your original scenario. And of course, you asked for my solution, and I live in the United States.

>>I could not disagree more. If I am the one doing both the internal and external work, the two are very related.
>
>Though they're related, It should be designed in such way that the external scheme determines the internal one, and not in reverse. So each different DBMS should have the same external scheme (at least as far is possible) and therefor could have different internal ones.

Says who? You have made this statement, but provided nothing to support it.

>In this case, I guess I am, it's a given fact when doing such projects you want tranparancy (Oh no walter what have you done, you've introduces another buzzword: transparancy) which means that you could, switch backend with the minimum amounts of efforts and/or costs. In your SQL 7 example, it might be also called anticipating on SQL Server 2000.

Again, you are making my point for me! If I want to switch backends with minimal amount of effort, I have to take into account that Oracle and SQL Server 7 handle cascade updates differently. So I can use surrogate keys and minimize the differences in my backends. And I can anticipate SQL Server 2000 all I want, but it isn't going to change the fact that I have to use SQL Server 7 now.

>OTOH, when doing things in a way just for the sake of consistency while other options might have a clear advantage (I think we now have proven that when working with intelligent keys, there could be advantages in using intelligent keys which are important for the system), it is not advisable to just skip this argument and use surrogate key just for the sake of consistency.

No, you have yet to provide an example of a clear advantage for initelligent keys.

>The main message I wanted to give here is: Use your brains, just think what strategy is best in your case and make a decision based on the advantages and disadvantages you'll find. Not ever, make just a decision based on "for the sake of consistency, I'll always apply surrogate keys".

There are many advantages to using surrogate keys, including the problem of cascading updates. Yet, you summarize my arguments as just "consistency", as Jim Nelson has tried to do.

>Consistency ??? You've got me wondering how you would you this for multiple back-ends. However this is not what I'm trying to say. The thing is that surrogate keys might seem to solve a few problems at first sight, but create others at the end. In the previous examples when using intelligent keys in ORACLE, SQL server (SQL server, use the references keyword, and when using the RI builder, in VFP) I could just define a restrict rule, and don't have to worry about writing triggers (which tend to be buggy anyways).

I love your arguments Walter. On one handle, you write that triggers are great when I should use them to handle cascading updates in SQL Server 7. Now, when it does not suit your scenario, they are buggy. Walter, you are being terribly inconsistent.

>About consistency and surrogates: I wonder if we all would agree to wear the same clothes and drive the same cars and eat the same food because it would be:
>1) consistent and standard
>2) simpler by far
>3) cheaper all around
>4) require less thinking
>
>Try to imagine such world, and more important what the implications are.

You are comparing designing database systems to wearing clothes and driving cars? Now, you have gone off the deep end. < g >

>Well, I personall have got a licence for that one, and an extra machine is never a problem for me. If neccesary I'll take my SQL-server from home (is already install on a server) and do my stuff. As for the configuration of the hardware. It is not important as long as SQL-server will run on it as its only purpose is to restore a backup and gather the data in there.

We have already been through this argument, and you have agreed that restoring backups can be a major PITA.

>>Your scenario is becoming more time consuming as this discussion goes on. One, your backup data is in SQL Server, so you have to restore it. I have covered the hurdles with this. Then you have to create a VFP program to access it.
>
>Using Views via ODBC would do in this case.

I didn't say you couldn't do it. Just the longer your scenario goes on, the more it is costing your client. I assume you are not working for free.

>The point is that marking data as inactive is not a suitable solution in many cases, and I can't help to 'feel' this isn't the right way. I'd rather take some other actions to maintain possibilities to recover data. You could think of storing older data in other tables or maintaining a backup strategy where recovering is made as easy as possible.

Why is it not suitable?

>O.K, apperenlty you've found a way to easely 'undelete' old data by marking it inactive. At least you've thought about this strategy and I admire that. (I like to see people thinking about their actions in stead of following the hurdle and hypes (and yes this also applies to the surrogate vs intelligent key thing)). However, since this case is so exceptional (at least in my opinion) it is not suited to draw general conclusions.

Why is this an exception?

>You're in a better position (because you appearently have thought more about this problem than I) to judge if its the best strategy. However, If I were you, I would find some other people who have simular problems and have found other solutions.
>
>No lets agree, to agee and regard our different opinion to be negligible

I much prefer to argue. < g >