Database Reference
In-Depth Information
Modeling Your Database
When you model your database, you decide what entities you'll be tracking, which ones de-
serve a table, and how they relate to one another. It's easier to create the right tables and con-
nections the first time than to go back and change them later, especially if you need to
change tables that already have data in them. The point of this exercise is to build a “blue-
print” to follow as you build your database. The pros call this blueprint an
entity relationship
(or ER) diagram.
Choosing Entities
The first step in modeling a database is to forget there's going to be a database. Focus on
what you
do
—the things you need to achieve and the information those tasks require. During
your workday, what do you need to produce for yourself? For your coworkers or customers?
If you hit a wall, what information might get you going again? Write down what you do,
what you
wish
you could do, and the results you require.
Figure 5-1
illustrates a first take at
this process.
With this list in hand, you can start to figure out what
entities
your database needs to track.
For each item on your list, think of all the
things
it involves.
Figure 5-2
shows a possible list.
Add those things to each item on your list. For “Track my time,” add “Time” and “Services,”
for example.
Your initial list should include all the entities you think are important. Try to evaluate every
angle of the tasks and the information they require. Once you have a list of entities, the next
step is to figure out which ones really matter. Consider each entity and get rid of the ones
that essentially duplicate an existing entity, are excessively specific, or are simply not worth
tracking in your database. This process will help you look at each entity from different per-
spectives as you come up with a final entity list.
Finding Relationships
Now that you have a list of entities, you need to figure out how they relate to one another. To
get started, just pick two of your entities—Customers and Jobs, for example—and ask your-
self how one instance of each entity relates to the other (if you need some guidance, see the
box on
Many for One and One for Many
). You might come up with this answer:
Any given
customer hires me to do jobs, and any given job is done for a customer
. That sentence tells
you two important things:
▪ Customers and jobs
are related
.
▪
One customer has many
jobs, but each job has only one customer.
