Figure 3.12 The final Meet_A_Geek database model.
Defining Relationships
Let's move on to the next step, which is to define the relationships. To define the relationships, look at the
whole picture you have drawn so far. Right away, you can see a relationship between customers and
orders—a one-to-many relationship. A customer can place many orders, but those orders all point back to
the one customer. Another relationship exists between the products and orders. This relationship is a many-
to-many relationship—one order can contain many products, and one product can be in many orders. From
what you know about many-to-many relationships, you will have to add another table to your database.
Another noticeable relationship is between orders and transactions. For every order, there is one transaction.
For every transaction, there is one order. This is a very nice one-to-one relationship.
In this step, you are supposed to identify the data types that you will be using. Because you won't cover
data types until Day 7, use simple types to express what should be used. Start with the Customers
table.
In the Customers table, you have a First_Name and a Last_Name field. From your business rules,
you know that these fields are required. To reflect this requirement in the database, you will make these
fields NOT NULL. NULL is best explained by telling you what it is not. A NULL value is not 0. It is not an
empty string, such as "". It is not a negative number. It is nothing. A field that contains absolutely no
value is considered NULL. So, to prevent a field from being empty, you can require it to be NOT NULL.
This forces the database to have a value in the field. Because these fields are names, and names are
generally letters, you define these fields as character fields. You do the same for the Address, City,
State, and Zip fields. You define the Email address field also as a character field. The Age field will
be a positive-only number (ages cannot be negative). The Gender, Race, Hair_Color, and
Eye_Color fields at first can be defined as character fields. On Day 7, you will learn that it would be
more efficient to have these as enumeration types. For now, define them as character fields. The other
fields can easily be defined as character fields, except for the Smoker field. You know from your
business rules that the Smoker field can be answered with only a yes or no, so you could define this as
a Boolean field. A Boolean type is a type that is either TRUE or FALSE.
You would perform the same process you performed on the Customers table to each of the remaining
tables. Also, remember that this is the time when you will add a key field to each of the tables. After you
have finished adding the keys, it is a good idea to model the database again. This will provide you with
the blueprint for tomorrow's lesson, which will be to actually create the Meet-A-Geek database.
Summary
Today, you learned the importance of correct database design. A database is like a building. It requires
proper planning and designing to stand the test of time. I introduced the database design process. This
process is made up of six steps. If this process is followed, the database that is created will be very solid.
Each step builds on the previous one. The first step is to define the business process. The next step is to
define the business objects. These objects will eventually become the tables that make up the database. The
descriptions of these objects will become the fields. The third step is to establish the business rules. These
rules govern how the data is stored and how the business objects interact with each other. The next step is
to model the database. Modeling helps solidify the structure of the database in your mind. It easily allows you
to see the errors and flaws in the design and logic of the database. The final step is to identify the
relationships between the objects. You learned that there are three different types of relationships: one-to-