Steps in Developing a Custom PHP/MYSQL Database System
Creating the Database ‘Map’
If an existing Microsoft Access, FileMaker or other database already exists, work will begin with a review of the current system, including creating a wish list of changes or new features to be added and ’mapping’ the existing system. If the goal is to create a completely new system to replace disconnected databases, spreadsheets, or a paper file system, the client will be walked through creating a database ‘map’ to determine the relationships between the different pieces of information.
Once the structure has been mapped out, a database can be created. This involves setting up fields for each piece of information, and establishing the relationships between the different pieces of information. A simple example could be a contacts and projects database; each contact includes a table with fields for first name, last name, etc. BUT each contact might have multiple projects associated with them in a separate table, linked to the appropriate contact by a unique ID number. Since some contacts might have only one or two projects, while others might have many projects, you wouldn’t want to simply create a fixed number of projects within the contacts table; you would want the ability to add as many as you needed. From there, the projects table might have other information, such as expenses, staff hours, or description of work done, which needed to be associated with each project. Again, you would put each of those into a separate table, so you could add as many new items within those categories as you needed to over time.
Creating the User Interface
Once the initial database is developed, the process of creating a functional user interface begins. This is usually more than half the project time! User registration and login systems may be developed, and data screens allowing users to view, add or edit data will be created. An initial navigation system is developed, allowing users to get to different parts of the database system, or to do different things. This design is tested by the client, to make sure that when they click a button they get what they expected, and when they generate a report it contains the information it should.
Adding Additional Features and Finalizing the Design
Next comes finalizing the design. Generally the client realizes that there are other ways they would like to access their data - going back to that Contacts/Projects database, perhaps they realize they would like to be able to generate not just reports based on specific projects (such as total hours and expenses), but also based on other things, such as total hours spent by a specific staff person during the previous week for all projects. They may want to set up restricted access levels, so certain staff can only access data related to ‘their’ projects, and not all projects. Or they may want to add new features, such as a communications log tied into both contacts and projects, to keep track of phone calls, emails or letters.
While some of this work may be built into the initial database development budget, it can increase the work (and cost) significantly. Therefore, the initial system should be designed in as modular and flexible a fashion as possible, to avoid the need to extensively re-write and test existing code when adding new features. And all code should be commented, to allow other programmers to modify or update the system in the future without having to spend large amounts of time analyzing the user interface or database structure to understand how it works before attempting to modify it.
Test, Test, Test
Finally, the database is extensively tested. People who don’t know how data ‘should’ be entered are asked to use it, to see how well it performs under real world conditions. Sample data is entered, and people are asked to generate reports or find specific information. Bugs and errors may appear that weren’t noticed before, and they will need to be corrected. Or the client may request changes and additional features because things aren’t working quite as they expected, or they realize how much more they could do with the system.
Port Data from the Existing System into the New One
The (second to) last step, if the system was built to replace an existing database, is importing the existing data into a ‘clean’ version of the new system. All information is exported out of the old database(s) and imported into the new one. Depending on how well the data exports, and how closely the new system corresponds to the old one, work may need to be done to ‘massage’ or clean up the information before bringing it into the new system, and keys (unique ID numbers) may need to be added to different types of data to maintain relationships. Depending on the size of the previous system and how different it is from the new one, some or all of the data may be re-entered manually.
Test Yet Again
Once the database is in actual use, it is effectively being tested on a daily basis. No matter how good the initial work was, glitches or unexpected behavior may be discovered. Depending on the client’s budget and scope of the original project, basic bug fixes may be implemented, or more extensive changes done and additional features added.
- Developing a Custom Database
- Land Trust Custom Database Screenshots