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.