A properly designed database provides you with access to up-to-date, accurate information. Because a correct design is essential to achieving your goals in working with a database, investing the time required to learn the principles of good design makes sense. In the end, you are much more likely to end up with a database that meets your needs and can easily accommodate change.
The database design process is guided by certain principles. The first principle holds that redundant data, or duplicate information, is undesirable because it takes up space and raises the possibility of errors and inconsistencies. The importance of accuracy and comprehensiveness in information is the second principle. Reports that pull data from your database will also contain inaccurate information if your database contains inaccurate data. Your decisions will therefore be misinformed if they are based on those reports.
A good database design is, therefore, one that:
Divide your information into subject-based tables to reduce redundant data.
Provides Access to the information it requires to join the information in the tables together as needed.
Helps support and ensure the accuracy and integrity of your information.
Accommodates your data processing and reporting needs.
The Design Process
The design process consists of the following steps:
- Determine the purpose of your database
This helps prepare you for the remaining steps.
- Find and organize the information required
Gather all of the types of information you might want to record in the database, such as product name and order number.
- Divide the information into tables
Divide your information items into major entities or subjects, such as Products or Orders. Each subject then becomes a table.
- Turn information items into columns
Decide what information you want to store in each table. Each item becomes a field and is displayed as a column in the table. For example, an Employee table might include fields such as Last Name and Hire Date.
- Specify primary keys
Choose each table’s primary key. The primary key is a column that is used to uniquely identify each row. An example might be Product ID or Order ID.
- Set up the table relationships
After examining each table, determine the relationship between the data in each table and the data in the others. If necessary, add fields to existing tables or make new ones to make the relationships more clear.
- Refine your design
Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design, as needed.
- Apply the normalization rules
Apply the data normalization rules to see if your tables are structured correctly. Make adjustments to the tables, as needed.
