There are many programs on the market today that will display your database. These are great, but in
the first draft, I prefer to do it the old-fashioned way. That way I can erase and add things quickly and
easily. After the database has been created, the diagrams that are produced by these programs are an
invaluable tool when trying to create queries or becoming familiar with a database schema.After the sketch has been completed, it is time to fill in some of the blanks. You might see some holes
that need filling, or maybe now you can see that some of the descriptions you used for one of the
objects fit better under another object.
After the dust settles from all the changes that you've made, it is time to start assigning data types to the
fields. The types you assign, and whether the field can be null or not null, can be determined, in part, by
the business rules you defined in the previous step. The types that MySQL supports are covered on
Day 7, "MySQL Data Types." For now, just understand that this is the phase in the design process
where data types are assigned.When you have completed this step, you will see the basic framework for your database. The tables, as
well as the columns, for the most part, will be defined. The next step will strengthen the existing
structure.Establishing Relationships
This is the last step before you create your database. Defining the relationships between tables is not always
an easy task. First, you have to determine whether a relationship exists. Second, if there is a relationship,
you must determine what type of relationship it is.
The easiest way to determine relationships is to look at the diagram that was created in the previous
step. Take one table/object and see if it logically relates or will interact with any of the other
tables/objects. For example, in the bookstore database, you have a customer, a book, and a transaction
object. I would first look at the customer and ask myself if it has any relationships or interactions with the
book object. In this example, it does. A customer must buy a book from your store to be a customer, so
a relationship does exist. Then I would ask myself the same question, this time with the transaction
object. Again, there is a relationship. When a customer purchases a book, it creates a transaction, so
there is a relationship. I would then take the book object and see if it has any relationships. It has one
with the customer, but it does not with the transaction object. A book will exist without a transaction. The
transaction object interacts with the customer, not the book. This all may seem a little confusing at first,
but, with time and experience, you will be able to establish relationships quickly and easily.The next step in this process is to determine what type of relationship exists. There are three types of
relationships in a relational database: one-to-one, one-to-many, and many-to-many.One-to-One
In a one-to-one relationship, a record in table one must have a record in table two, and if table two has a
record, a corresponding record must exist in table one. Figure 3.2 demonstrates this.
Figure 3.2 A one-to-one relationship.
In the bookstore example, a one-to-one relationship might exist between the order and the transaction
table. For every order, there must be one transaction, and every transaction must have an order. To
create this relationship within the database, you must add a field that will house this relationship. The
field that normally does this is called a key field. Key fields are discussed in more detail on Day 11,
"MySQL Table Locks and Assorted Keys." For now, just understand that a key field helps define
relationships, among other things.
The key field is a unique field within the table. No other record will have the same value in this field. The
reason behind this is to distinguish a record from all other records in that table (see Figure 3.3).