>Now that we're re-writing our application from scratch, I want to do the database design right. Currently, just about all of the tables contain the client ID plus the client name, and sometimes even the phone number.
>
>I know with normalization you should just have the client ID because the rest can be found in the clients table. But what do I do when I have that table in a grid and I need the name to show? Wherever the user is inputting stuff, s/he will want to know the name, not just the client number.
>
>I thought about using SQL to make a new temporary table that has the name from the clients table, and the rest from whatever table is being used. But then any changes will be made to the temp table. I don't want to have to do a seek on the regualr table to find the corresponding record everytime a value is changed!
>
>I'd like to avoid duplication the info, because now if the name or phone number changes, I have to search through all the tables and change all the occurances. But I also don't want to lose the ability to edit the table directly.
>
>Any suggestions?


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.