Designing a Database

1. What is the purpose of the database?

You need to know what information you want from the database. From there you can determine what facts you need to store about each subject (the fields in the tables), and the column heads (tables) under which you need to store the facts.

2. What tables do you need?

Sketch out and rework the design on paper first. The fundamental design principles say:

• Each table should contain information about one subject. When each table contains facts about only one subject, you can maintain information about each subject independently from other subjects.
• A table should not contain duplicate information, and information should not be duplicated between tables.

3. What are the fields that you need?

While sketching out the fields for each table:

• Relate each field directly to the subject of the table.
• Do not include derived or calculated data.
• Include all the information that you need
• The information should be stored in the smallest logical parts like “first name” ,”last name” instead of “Name”.

4. Identify the Primary key?

Each table in the database must include a field or set of fields that uniquely identifies each individual record in the table. This field is called a Primary Key.

5. Determine the relationship between tables?

To bring related info back together again in meaningful ways a relationship can be set up on the basis of common fields in your tables.

6. Refine your design.

It is easier to change your database design at this point in time than later after you have filled the data in the tables.

7. Enter data and create other database objects

When you are satisfied that the table structures have met the design principles described here, then it’s time to go ahead and add all your existing data to the tables.

Relational Databases

After you've set up different tables for each subject in your database, you need to bring the information back together again. The first step in this process is to define relationships between your tables. A relationship works by matching data in key fields —usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table.