>The fact is, all short comings of either approach are manageable. Just use views and surrogate keys. This argument happens everytime this topic comes up. The real issue is which approach has less problems. Views don't take considerable time to load, unless you're trying to do something stupid like pull entire multimillion record tables to the workstation.

IMO, this not entirely correct. Sometimes you want to have your view containing a few thousand records. You would say this should not be a problem.. But when having a view with 4 inner joins and 2 left joins (because of all the surrogate keys), and trying to load this one on a workstation on a 10 Mb network, it takes a while to load. When doing the same with normal tables related with SET RELATION and a filter, it loads instantly, but performs less in grids. Well what are your preferences ??? I would say when using grids... well go for views. However when doing calculations, a SET RELATION and SET FILTER might outperform a view by a mile.

>The old fox browse never did that. It had a means of extracting just enough data to display until you "requested" more by scrolling down.

Yeah, I know. I think this is the biggest disadvantage of views.

>In your example of sorting by the description field, how many records and fields are in the tables? A view can be indexed on the description by the client machine. That precludes a need to maintain another index that slows performance when these indexes are to be updated.

>There are basically two approaches. 1) the surrogate meaningless keys 2) the meaningfull keys. Each approach has problems If you built a line item with this meaningful key...

>InvoiceNumber+LineNumber

>If the invoice number changes, you are not just doing a simple cascade replacement, but a replacement of part of the key.

ehhhh, you're are doing a replacement on the invoicelines table. A simple replace on the invoiceheader table would automaticly cascade the changes to the invoicelines table. I can not imagine than the invoicelines table has childs of its own, so a replace on apart of the key is not a problem at all.

But I take you point. Handling composite keys is more difficult (I never use cascading composite keys) but I think the VFP RI builder does handle them quite well. However I agree, when dealing composite keys you might better use surrogates.


>Walter was suggesting that we don't think about this stuff. That's because supporting both ways of accomplishing a task is inefficient.

No, having a 6 join view is efficient ?? The user must pay (in terms of performance) because in sake of consistency we are not willing to take another viable approach ?? I don't buy this, nor do lots of people.

>Rehashing this topic over and over is also inefficient.

You tell me... How many times should I speak up to convince you people ;-)

>I was very frustrated by the grid's lack of Rushmore Optimization support, but now I use views and I only pull the data people need and you know what? I don't care anymore. Performance is good and users are happy.

I'm glad it works out for you. In my everydays work, I'm constantly asking myself whether I should use views or tables directly in a particular case. Sometimes the view wins, sometimes the direct table approach wins. I'm willing to do lost of test and research in determining the righ approach and I'm really critic when benchmarking the results. I don't care if it takes a few hours more to tune performance to satisfy my clients.

>It seems more sensible to master one approach.

I don't agree. This leads to lots of misunderstanding about the two concepts. How can you promote one side when you don't know the details about the other ?

In the case of Don, intelligent keys are probably the best choice. He does not use a database container (with all his problems) and has tried something in the surrogate key arena and run into some problems. Talking him into using a database container and using views with all its different properties and sides, would be even a more difficult path for him. To learn more about the concept, he might better begin with the easiest approach.

The whole problem with this surrogate keys stuff is that whole tribes are talked into it by guru who not seldom have no experience in the field or what soever (Date and Codd, related topic: Normalisation vs denormalisation), and never learn to research other alternatives, its advantages and disadvantages. From an idealogical point of view surrogates are in the vast-majority of cases the best solution, but when handing in practice, you'll soon run into problems like a poor performance, a more troublesome road when debugging, (end-users or adhoc) reporting and troubleshooting. And for what ??? Only to avoid cascading updates or sometimes being more space conservative ?????? It sometimes looks to me using a axe to open a can.

Example:

Table1 (*=Intelligent keys )

Personelno, *
Name,    
Adres,
DayOfBirth,
HireDate,
Sex, *
Adres,
City, *
Zipcode, *
State,   *
Telephone,
Scale, *
Department, *

All the 7 fields are intelligent keys (yes, even the Sex field). Now lets imagine you've got a thousand persons in this table and you want to print all this info on a report or list in a grid. With surrogate keys you'll have to include Seven joins whereas with intelligent keys it would be sufficient to only list the table. Imagine the City, ZIP, Personel table lists a few thousand records the scale and city table about 50. You can't tell me this one is going to load instantly.

Well if you're trying to tell me that you people do think about this subject, then tell me how would you construct this database, and why did you choose a surrogate in one instance, and a intelligent in the other. The answer to this question explains my standpoint.

For all those guys who say that they consistently use surrogate keys. Tell me how often did you use a character C(2) to store the state when using a state table ? And if you're in a situation when you have to define a sex table and store some properties in that. Are you going to use a surrogate key ??

regards,

Walter,