Redesigning a FoxPro database/program using Access and Visual Basic
Designing a program from scratch in many instances is
an easier task than modifying an existing solution. A recent project started as a request to take data from a
program and display it on the web. As a future consideration the data displayed on the web page
would eventually be updated by customers via a web browser. This article will explore the design of
the database while discussing the use of some tools to facilitate the process. The most notable tools used in
this redesign were Visio 2000 and SQL’s DTS.
As a starting point, the existing solution was analyzed. The
original user interface
was built using visual FoxPro and consisted of 17 tabs. The interface updated a FoxPro database. The
FoxPro database was not normalized; there were 5
main tables containing no less than 8 types of addresses. In addition there were numerous columns to
holding phone numbers, e-mail addresses, and contact information.
After looking at the current solution and discussing
issues with the users the best solution would be to redesign the database.
The reasons for the decision were to facilitate future development and ease
the movement of data to and from the web. The current database was not flexible
enough
to meet the demands of the user because there was no normalization of the data. Each time
the user wanted to add a new piece of information a field had to be added
to the table and then an input area had to be added to the user interface. From a
programming standpoint making changes was straightforward but from the user perspective it was frustrating to wait for programming changes to be performed before
adding another piece of data. When moving data to the web it would be easier to use a drop-down list of address
types and then present the browser with one of the addresses. If the original database is
used each address would have to be coded individually.
Before beginning the redesign stage some research was in order.
There is a nice series of articles on the
SQL Server Magazine site. There are 6 articles in the series all written by Michelle A. Poolet.
The articles are a great starting point for database design. It is sometimes difficult to follow all the
suggestions but it is nice to be reminded of the steps needed for a solid database design. Also, remembering
the separation between physical design and logical design helps keep perspective while going through the
analysis.
Once the data was organized Visio 2000 was used to design and generate an Access
database. Using Visio provides flexibility while allowing one to create and delete relationships between the
tables. There were some caveats to using Visio when using the database modeler. When an object is deleted from a Visio
database drawing the object is not necessarily removed from the project. This setting is controlled under Database
–> Options –> Modeling on the logical diagram tab one can set the behavior when deleting
objects from the drawing (i.e. whether the object should be dropped altogether). The existence of objects in the
model that were deleted from the drawing can cause errors when generating the database. To make sure the
design process is working check the model for errors during the design. This can be done using the command Database –>
Model –> Error Check. Eventually, the new database was generated.
After generating
the database it was time to move the data. To facilitate the database migration SQL Server 7.0’s Data
Transformation Service was used. Although I had used Visio many times I had never generated a database
using Visio. My experience with DTS was similar; I had never used it to migrate an entire database. It
was interesting to note that in the beginning each address was transferred individually. After stepping back
and taking a look at the transfer process I realized the data could be moved more quickly using the UNION
command to link my select statements. This allows one to transfer all the address information in one step instead
of creating a transform for each group of addresses.
With the data now residing in a new database with better
normalization it was time to create the user interface. The data was stored in Access with the idea that an Access data
project or Access forms would be the best way to design the user interface. Data from Access can be upsized
to SQL 7.0 and Access provides a nice development area before moving to SQL for the final application. After further analysis
it was determined that Visual Basic would provide the best solution for building the user interface. The
data, however, was left in Access and will be migrated at a later date.
Finally, creating the web data display forms and input forms must be added. This process will be facilitated by
a better database design and by porting the classes used in the Visual Basic program to ASP classes. This reutilization
of code is a benefit of the redesign. The movement to Visual Basic from FoxPro facilitates the development
process by eliminating the need to recreate code for each application change. The modification of the code to work
on the web will be much simpler using Access or SQL than it would have been if we had used FoxPro.
This document provides an overview to the steps used in redesigning a program. The redesign involved breaking out
the existing solution to a data, business and presentation layer. This facilitates future development while providing
flexibility to the final program.
