Designing the Tables

• Before you start thinking about tables, you need to determine what information the database is going to store. The easiest way to figure this out is answer who, what, where, when, and how. For example, when designing a database that tracks students, what classes they take, and who teaches the classes, the following would be some of the answer to the questions: Who: students and instructors; What: class, grade and number of credits; Where: class locations, student address and instructor address; When: class date and time; How: how is the class delivered.
  
  It is important to take time and answer these questions as completely as possible. These questions identify the fields of your tables. Once you know what all of the fields are, you are ready to start arranging them into tables. To make sure that the field directly relates to the table name, group the fields by the common theme and break the fields down into the smallest pieces possible. For example, instead of a field called 'InstructorName,' you should use 'IntructorFirstName' and 'InstructorLastName.' Some fields might not clearly belong to any one table. For example, the field 'Grade' relates to the student, but it also relates to the class. When you encounter a field that belongs to two different tables, it means that it needs to be in a join table, a table that joins two tables in a many-to-many relationship. That way, a student can receive different grades for different classes that are taken.
  
  

Designing the Relationships

• Once you have your tables designed, the next step is to figure out how they relate to each other. There are three types of relationships: one-to-one; one-to-many; and many-to-many. The one-to-one relationship is not frequently used. It means that one record in one table directly relates to one record in the second table. This relationship is used in Human Resources databases, where one table contains the non-secure data such as name and address, and the second table contains the secure data such as salary.
  
  The second type of relationship, and the most common, is the one-to-many relationship. This means that one record in one table directly relates to many records of the other table. For example, one instructor can teach many classes. This is signified by the primary key in the instructor table, 'InstructorID,' relating to a foreign key in the classes table, 'ClassInstructor.'
  
  The third type of relationship is the many-to-many. In this relationship, many records in one table are related to many records in another table. For example, one student can take many classes, and one class can have many students. To create this relationship, a join table is required. The join table actually contains the foreign keys. In this relationship, the foreign keys would be 'StudentID' and 'ClassID' in the join table. By creating these relationships, you are allowing data to flow from table to table and be easily accessible.