July 11, 2008


The basic principles of relational database design. Part 1

Filed under: Database, MySql — admin @ 10:17 am

Source: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez

An important aspect of most every business is record keeping. In our information society, this has become an important aspect of business, and much of the world’s computing power is dedicated to maintaining and using databases.

Databases of all kinds pervade almost every business. All kinds of data, from emails and contact information to financial data and records of sales, are stored in some form of a database. The quest is on for meaningful storage of less-structured information, such as subject knowledge.

The most important steps of the relational database design:

  1. Define mission statement and mission objectives for the database. The mission statement defines the purpose of the database. The mission objectives define the tasks that are to be performed by users against the data in the database.
  2. Analyze the current database. You identify the data requirements of the organization by reviewing the way data is currently collected and presented and by conducting interviews with users and management to determine how they use the database on a daily basis.
  3. Create the data structures. You establish tables by identifying the subjects that will be tracked by the database. Next you assign each table fields that best characterize its subject, and you designate a Primary key as well. Then you establish field specifications for every field in the table.
  4. Determine and establish table relationship. You’ll identify relationships that exist between the tables in the database and then establish the logical connection for each relationship using Primary keys and Foreign keys, or linking tables. Finally, you’ll set the various characteristics for each relationship.
  5. Determine and define Business Rules. Next you conduct interviews with users and management to identify constraints on the data in the database based on the way the organization views and uses its data. These constraints are then declared as Business Rules, which will serve to establish various levels of data integrity.
  6. Determine and establish Views. Users and management are interviewed to identify the various ways they look at the data in the database. After these various perspectives have been identified, you establish them as Views. Each View is defined using the appropriate table or tables, and certain Views use criteria that limit the records that they display.
  7. Review data integrity. This phase involves four steps. First, you review each table to ensure that it meets proper design criteria. Second, you review and check all field specifications. Third, you test the validity of each relationship. Fourth, you go over and confirm the business rules.

>> Part 2

175 Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment