Jim, see below ...

>Peter,
>
>While I find myself agfreeing with most of what you said there are a couple of things I feel the need to comment on.
>
>>Summarizing it all : when the logical datamodel is normalized properly 100 % AND you apply this to the physical model, you will be having more tables opposed to less normalization, you will be having proper PK's, you won't have ANY CK, and you will be having FK's on all the users will ever need to sort upon.
>
>Your reference here to CK's is totally offbase. Having a CK has nothing to do with normalization at all. It is totally possible and acceptable to have a table strucutre with more than one set of fields that satisfies the requirements for being the PK. Whenever this happens you have a CK. If you ignore the existance of the CK then you can never reach Boyce-Codd Normal Form( which immediately follows Third NF).

So let's have BCNF;
Luckily I dealt with this one the other day, and so far I feel strong ;) However, I know I am challenging here, and expected some response. So :

BCNF is all about candidate keys, at least, it looks like it. Looking for the good examples and explanations is rather difficult (I found the other day), but, in the end I found some (over the Internet). Now I'd like to ask you Jim, prepare me some (whatever NF) indluding CK's, and I will work it out for you. Agreed ? when I can I win, when I can't you win. Not that someone wants to win, but I'm prepared, and I state that BCNF doesn't even exist. Reading the description(s) carefully, it's already in the definition itself. Your turn.


>
>>Relators should have two logical fields only for the FK's, and a normal PK over it, and an additional reversed index.
>
>Again, with normalization there is no reference to using Intersection tables at all. Intersection tables are only *** required *** for resolving many to many relationships. Although you can use them anytime you like, if you are dealing with a relationshiop that can never become many to many you adding unnecessary complexity to your data design.

I'm not sure what you are stating here, though I feel you are right somewhere;
Indeed, when a relation won't ever be N:N, there won't be a relator, not in the logical model, and not in the physical model too, though it can be useful;
I think you thought somewhat ahead of what I was saying, and you derived something from it that might outsmart me. But, I didn't say why there should be two logical fields only in the PK of the relator. I assume you have your own thoughts on this, and they just as well could be right. But I don't think so. But please let's wait first for your CK example; I suppose the answer comes out of that automatically.


>
>>One thing (where I'm about to loose) : it is all native VFP tables, with the 2GB as a pain in our neck. So we are converting to a remote DBMS, and it is there where the views will come in. I'm pretty sure response will be worse, though nobody will believe that.
>
>You may be surprised. The speed of VFP versus database servers has changed. Used to be VFP was hands down faster, that is no longer the case today. SQL Server 2000 can as fast and even faster than using VFP tables over a network. I am not familiar with Oracle but I would venture to guess that the situation there is similar.

I already knew, or assumed anyway. But, I am more talking about the logical level which is hard to explain, and can be understood only (easily) when the base is a native table'd app. Just one stupid example : Native tables allow for the A. Skip 1 or B. Seek.

A.
The Skip 1 (or -1) is at will of the user, and the number of records downloaded must be in belance with how many of the Skip's will occur in the one set downloaded. I.e., having the set to 1 record won't be very fast. Having it at 20 may be 19 too many, when the user asks for 1 only.
IMO there should be a balance somewhere between the block downloaded (containing a number of records anyway) and the resultset-amount. Maybe this 'll come to 1:1.

B.
Will always generate overhead only, unless there is some other balance in first downloading some resultset, and perform the native Seek's in there.
Now tell me where the balance is, and I don't think it can be found, because all is up to the user, typing the Item number etc. and the description must be shown.

From A and B follows (up till now, for me ...) that I can decide for one think only : just perform a stupid SQL call per original Skip or Seek. And of course, any function of which is known that it'll show a complete screen of records can be that intelligent to have a larger restultset. But what about all the thousands of other "peek's" into the database ? it will be overhead only, unless you tell me that there is no overhead over whatever connection to the remote DBMS, opposed to the "connection" to the native VFP table residing on the fileserver.

So that's what I meant, and the internal speed of the DB-server won't be able to have an overall better performance (I guess); there are just too many peek's (Seeks), and most of the tables are just too large to download them (via a view or whatever).

>
>>I honestly think you are wrong here, and maybe you didn't test it properly;
>>with the non-remote dbms (i.e. native VFP tables on the fileserver) optimization is done within the PC.
>
>Here you may be mistaken, Rushmore uses indexes for optimization, hwoever it is not necessary for the Worekstation to download the entire CDX file. VFP is smart enough to understand the structure of its indexes and use only the amount it needs. VFP also does a lot of caching of data in the background for optimization.

I really start to feel possibly being wrong on the subject. But, I won't believe it until I can reason it out myself. And I can't yet.
BTW, please note the small misinterpretation I had with Hilmar, I expressed in the before Message;
How would it work ?
So we have this index, and we know that where we use (I'll keep it native) a Seek followed by a Locate While (or Scan or whatever), all (index) records in between are needed. Thus, without a Filter, all records are just needed, and all will be read and come over the line.
Now WITH Filter, still using the index :
A filter on what ? a leftmost part of the key ? yeah, very smart, but I can do that myself by the Seek and While. So that's not it.
Then we have a right part;
Now you are going to tell me that VFP (thus within the PC) can tell from the index-data received what the next block to download will be ? That's a hard task but maybe it's true. So please tell me it is; I'd like it.

Try this for experience :
Have a 1M record file, Go Top and Skip 100,000. Wait a little, and you can proceed. Assumed that all is in the cache of the PC, skip again within the received 100,000 record boundaries and notice that the answer is always immediate. There is no way this is solved by the memory-only operation, because it's too fast for that. I'd say this is Rushmore doing it (I don't even know whether Skip is optimizable, and never shut Rushmore down in order to check). Now supposed I'm right, why did I have to wait that long the first time ? Don't tell me because the network was slow.

But honestly ... ready to learn.

>
>With Rushmore optimization the largest factor in performance IS the number of records in the result set. Also Rushmore applies both to SET FILTER and Views equally.
>
>>Now I think of it : remember Jim Nelson's VFP8 whish "a server object" (or so) ?
>
>Hmmm... I have one of those on my computer. It is named SQL Server

Yeah, guessed that. But Jim's idea was about having a native VFB DB as remote DBMS, and I liked the Rushmore part of it. It will even Rushmore the requests of several users for access on the same table. But :
When SQl-Server (etc.) is doing about the same, this (Rushmore) wouldn't be an argument. And since SQL-Server is MS, we can expect it to be there, can't we ?