The TASTRADE sample application has an "autonumber" key generator which performs like the Autonumber data type found in Access. If you look at the surrogate key field of TASTRADE tables, you'll notice that the "default value" of the field is NewID(field) which is a stored procedure. This procedure makes use of a table in the database called "keys" or something like that. The keys table stores the next valid key for every table. NewID() gets the key for a table and increments the value in the keys table. This is the system I use.

A couple of things about this system: I am still stuck in vfp3, which has that bug in combo boxes which makes them store listindexes instead of values when the bound column is a numeric value such as one of these keys. I like to do things in the conventional way, using tables with these surrogate keys to populate combos, having the bound column be the key field, having the key of the selected item be the value passed to the controlsource. To solve this, I made my surrogate keys into strings, by taking key = STR(oldkey) in my version of NewID(). Unfortunately, all my queries run a lot slower as a result. When I get vfp5 or 6, the first thing I will do is change the keys back to numeric. By the way, I don't remember how TASTRADE handles this. One odd thing about TASTRADE is that surrogate keys are used in some tables, and natural keys in other tables. Another thing about the system is that you can add a record with a simple APPEND BLANK and the stored procedure makes the key for you. I often use APPEND BLANK for that reason. If I had many simultaneous users, that would be a bad thing, since APPEND BLANK locks the whole table, I think.

About parameterized views: the main reason I use them is for displaying two tables related many-to-many, one table in the form, the other in the grid. You could join table2 with the common child table3 in one simple view, make that view the child of table1, and display the view in the grid. However, it says somewhere in the vfp books that you get poor performance joining a view as a child.

Make a parameterized view with the parameter = the keyfield for table1. In your nav button code you'll have to REQUERY the view every time you move to a different record in table1. You might also want to be careful about which fields in this view are going to be updateable.

Another choice, if table2 is small (a few dozen records) is to put the common child table3 in the grid, and put a combo in the grid whose controlsource is the table3.table2parentkey, whose rowsource is table2, and whose bound column is table2.surrogatekey. Combos in grids are very slow and should not be considered if the rowsource (table2) is more than just a little lookup table. Even 100 records will be too big.

>I never thought of having a key that wasn't visable. But that is a good point. Then I don't have to worry about what happens if they change the client id. How would I go about generating this integer id, then? With the client ID, the user typed it in so all I had to worry about was making sure it didn't exist already. I'm not sure how to generate unique keys for all my tables.
>
>Views seem to be the way to go. I think that was the unanimous answer.
>
>I remember the text mentioning parameters in views. I'll have to look at that more thouroughly. As to navigation buttons, I can see why they would be useless in a large table, but they're not bad for some of the smaller tables.
>
>I don't know anything about client-servers. We're able to just use the table in its entirety with no problems.
>
>Your last paragraph is way over my head. I'm not very familiar with SQL. I know just enough to be dangerous. :)
>
>Thanks,
>
>-Michelle
>
>
>
>>I would suggest, first of all, using a unique system key for all tables. This is a value that is never seen by the user but is manipulated by the system. An integer makes a great key because retrieval based on an integer key is very fast. Some people using a common naming scheme like nID or cID based on whether the key in numeric or character. Although your Client ID is a key you can make it a candidate key. If it is seen by the user, they may have reason to change it and if they do, your primary integer is still safe and unchanged.
>>
>>By using views, any table that has the client key as a foreign key (not the client id field!) can be joined back to the client name table so that you can display names and phone numbers quite easily.
>>
>>I would also suggest using parameterized views and avoiding navigation buttons. For instance, in your client table the parameter(s) might be first name, last name, phone number etc. and these parameters are used to find a client. You don't ever need to do next prev etc. When you want another client, you 'find' them by entering the parameters on a parameter screen.
>>
>>The parameters will retrieve one client at a time into your view.
>>
>>Why do this? In VFP even with 5,000 clients it is easy to skip next and previous. You basically open the dbf and go for it. But if your application is Client Server you don't want to download 5,000 records every time you open the client screen. So build your application in anticipation of C/S and think data sets. If you use views everywhere it can be very very simple to switch to SQL Server or Oracle with barely a change in your code. The big thing is only retrieving the data you require and not simplying opening a table and having all the data at your disposal. Using integer keys in a DBF are easy to change to Integer Identity keys is SQL Server.
>>
>>If all of your data handling goes through a class that does a save, etc. you can build in retrieval of the newly inserted primary key value either as a VFP stored procedure or an @@Identity SQL passthrough call to Sql Server.