>We have a bit of a debate going on in the office right now and will be very grateful for peoples feedback.
>
>We are currently planning a new application and want to make sure we can upsize to SQL in the future, this is our first attempt at an upsizable application.
>
>As a result we are considering attempting to access all tables through views.
>
>1. Should application specific tables such as our application registry be also be a view. Does this degrade performance.
>
Does a view make sense, either to filter some subset of records, present a join with another table, or to permit data entry without automatically triggering field or record-level validation rules that live in your .DBC? If so, then views, particularly parameterized views, may make sense. Not all access demands the use of views, but it makes parameterized retrieval of recordsets easy, can prevent users from directly updating the data table, perform joins so that the join is presented as a flat table without lots of SET RELATIONS/SET SKIPS, and permitting you to enter data for new or updated records and validate them before submitting them to the database, whose stored procedures form the basis of validation in a less controlled fashion.
>
>2. Is it overkill planning to treat our small lookup tables as views.
>
Not at all; views can provide filtration of values through parameterization and present data sets in a variety of sort orders without using SET ORDER and working against live tables. If there are a number of distinct ways of presenting the data, views can provide easy ways of directly referencing them by name rather than via SQL Select or SET ORDER. In some cases, it permits a single table to be used to hold several lookups in a single table, adding a field identifying which set of lookup values make up a specific lookup table, and permitting you to extract lookup sets by name, sharing a single form to maintain sets, and offering easy methods to combine distinct lookup sets into a single aggregate item list.
An argument against views is that they require a .DBC; if you have lots of static lookup tables, they can be copied to the workstation and referenced as free tables from a local disk. This will reduce network traffic, and in most cases, local drives are faster than a LAN, and especially a WAN. If you rely on native VFP tables handled by the workstation, this is a real issue.
>
>3. Certain views we create will mean recreating indexes for them at system start time. How big should these views be before its worth while bothering with an index. (I know Rushmore will create one when necessary)
>
Impossible to say; the speed and available memory of the local system, and the frequency that SEEKs, optimizable set-oriented operations, presentations in multiple sort orders and relational JOINs being performed against the view will decide this for you. Typically, my p-views tend to be small, and showing them in a standard order seems to be acceptable for data entry/customer service types of operations; ad-hoc queries against large p-views, especially where it's probable that the user will continue referencing it, I prefer to build obvious indexes up-front on initial use for things like analytic and management decision or review tasks, and for repeated batch processes like period closes and reporting.
The use of views does simplify the move to a backend on an initial basis; after upsizing data to the backend, query processing for views can be switched to remote views in some cases (eventually, you end up re-writing things either as stored procedures or to directly use SPT to leverage features not available with VFP directly, or to improve data access performance) with relatively little work. I've found that many clients are asking that the application be developed to use the MSDE for the primary database schema, relying on SPT or ADO for handling non-static tables; their take is that it's easier to port to SQL Server from the MSDE than from VFP and VFP .DBCs, especially in terms of triggers and SPs.
