- The Importance of Good Database Design
- Types of Table Relationships
- Understanding Normalization
- Following the Design Process
- Summary
- Q&A
This chapter is from the book
This chapter is from the book
This chapter is from the book
Following the Design Process
The greatest problem in application design is a lack of forethought. As it applies to database-driven applications, the design process must include a thorough evaluation of your database—what it should hold, how data relates to each other, and most importantly, is it scalable?
The general steps in the design process are:
Define the objective.
Design the data structures (tables, fields).
Discern relationships.
Define and implement business rules.
Create the application.
Creating the application is the last step—not the first! Many developers take an idea for an application, build it, then go back and try to make a set of database tables fit into it. This approach is completely backwards, inefficient, and will cost a lot of time and money.
Before starting any application design process, sit down and talk it out. If you can't describe your application—including the objectives, audience, and target market—then you're not ready to build it, let alone model the database.
Once you can describe the actions and nuances of your application to other people and have it make sense to them, you can start thinking about the tables you want to create. Start with big flat tables because, once you write them down, your newfound normalization skills will take over. You will be able to find your redundancies and visualize your relationships.
The next step is to do the normalization. Go from flat table, to first normal form, and so on, up to the third normal form if possible. Use paper, pencils, Post-it Notes, or whatever helps you to visualize the tables and relationships. There's no shame in data modeling on Post-it Notes until you're ready to create the tables themselves. Plus, they're a lot cheaper than buying software to do it for you, which range from one hundred to several thousands of dollars!
After you have a preliminary data model, look at it from the application's point of view. Or look at it from the point of view of the person using the application you're building. This is the point where you define business rules and see if your data model will break. An example of a business rule for an online registration application is, "Each user must have one e-mail address, and it must not belong to any other user." If EmailAddress weren't a unique field in your data model, then your model would be broken based on the business rule.
After your business rules have been applied to your data model, only then can application programming begin. You can rest assured that your data model is solid and you will not be programming yourself into a brick wall. The latter event is all too common.