Designing a database is no easy task; proper planning not only can help a database store data efficiently, but also can reduce the possibility of returning incorrect results in a query or report. Before creating a database, you should plan its structure. The following steps guide you through the process of planning a database structure. Apply the guidelines in this exercise by planning the structure of a new database for a scenario of your choosing. Possible scenarios might include a database that a school might use to keep track of students and their grades or a database used by sales representatives in a small business to keep track of their customers.
a. Identify the tables and objects to be stored in the database. For example, if you are creating a simple database that keeps track of students and the grades they earn in each class, you might want to store information about each student (identification number, name, address, phone number, date of birth, etc.), information about each course (course identifier, title, instructor, day and time offered, etc.), and the grades that students receive in each course. Tables should store groups of related information; that is, every item the table stores should have the same characteristics. Information about students and courses should be stored in separate files or tables because students and classes have different characteristics. For example, students have a date of birth and courses do not.
b. Identify a unique identifier (primary key) in each file or table that will uniquely identify each record. In a table containing student information, the student identification number should uniquely identify each student. First or last names do not make good primary keys because multiple students might share the same name.
c. Determine the fields for each file or table. You should create a separate field for each item on which you want to sort, search, or filter. For example, you should store first names and last names as separate fields so that you can sort students by first name and/or last name.
d. Determine the type of data each field will store. If a field will store data that might be used in a calculation, that field should be stored using a numeric data type. If the field will not be used in a calculation, it is not necessary to store the field using a numeric data type.
e. Determine how the files or tables are related. This database contains information about students and courses. The relationship between these two categories of data is that students enroll in courses and courses contain students. In this example, students can take multiple courses, and courses can contain multiple students. This often is referred to as a many-to-many relationship. It is good practice to remove many-to-many relationships from the database, and this often is done by creating an additional file or table. The new file or table should contain the primary keys of each of the two tables in the many-to-many relationship. In this example, the new table would contain the student identifier and the course identifier. This new table now accurately depicts which students are enrolled in each course.
f. After creating a new table, determine what additional information might be stored in the table. In this example, students receive a grade in each class they take, so it would make sense to also store the grades in this table.
g. Review the database structure and make sure you are not storing the same data multiple times. For example, because you store the student’s full name in the Student file or table, it is not necessary to store the name in the table containing the courses in which students are enrolled. Having redundant data can increase the potential for data to be updated in one location but not the other.
Exercises
1. Why is it not ideal to store redundant data in a database?
2. What problems can occur from having a database that is not designed properly?
