PHP 5/MySQL Programming- P77 ppsx
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (183.77 KB, 5 trang )
This page intentionally left blank
I
n chapters 9 and 10 you learn how to create a basic database and connect
it to a PHP program. PHP and MySQL are wonderful for working with basic
databases. However, most real-world problems involve data that is too
complex to fit in one table. Database designers have developed some standard tech-
niques for handling complex data that reduce redundancy, improve efficiency, and
provide flexibility. In this chapter you learn how to use the relational model to build
complex databases involving multiple entities. Specifically, you learn:
• How the relational model works.
• How to build use-case models for predicting data usage.
• How to construct entity-relationship diagrams to model your data.
• How to build multi-table databases.
• How joins are used to connect tables.
• How to build a link table to model many-to-many relationships.
• How to optimize your table design for later programming.
D
a
t
a
N
o
r
m
a
l
i
z
a
t
i
o
n
11
CHAPTER
360
P
H
P
5
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e
B
e
g
i
n
n
e
r
Introducing the spy Database
In this chapter you build a database to manage your international spy ring. (You
do
have an international spy ring, don’t you?) Saving the world is a complicated
task, so you’ll need a database to keep track of all your agents. Secret agents are
assigned to various operations around the globe, and certain agents have certain
skills. The examples in this chapter will take you through the construction of
such a database. You’ll see how to construct the database in MySQL. In chapter
12, “Building a Three-Tiered Data Application” you use this database to make a
really powerful spymaster application in PHP.
The spy database reflects a few facts about my spy organization (called the Pan-
theon of Humanitarian Performance, or PHP).
• Each agent has a code name.
• Each agent can have any number of skills.
• More than one agent can have the same skill.
• Each agent is assigned to one operation at a time.
• More than one agent can be assigned to one operation.
• A spy’s location is determined by the operation.
• Each operation has only one location.
This list of rules helps explain some characteristics of the data. In database par-
lance, they are called
business rules.
I need to design the database so these rules
are enforced.
IN THE REAL WORLD
I set up this particular set of rules in a somewhat arbitrary way because they
help make my database as simple as possible while still illustrating most of the
main problems encountered in data design. Usually you don’t get to make up
business rules. Instead, you learn them by talking to those who use the data
every day.
The badSpy Database
As you learned in chapter 9, “Using MySQL to Create Databases,” it isn’t difficult
to build a data table, especially if you have a tool like phpMyAdmin. Figure 11.1
illustrates the schema of my first pass at the
spy database.
At first glance, the
badSpy database design seems like it ought to work, but prob-
lems crop up as soon as you begin adding data to the table. Figure 11.2 shows the
results of the
badSpy data after I started entering information about some of my
field agents.
Inconsistent Data Problems
Gold Elbow’s record indicates that Operation Dancing Elephant is about infil-
trating a suspicious zoo. Falcon’s record indicates that the same operation is
about infiltrating a suspicious circus. For the purpose of this example, I’m
expecting that an assignment has only one description, so one of these descrip-
tions is wrong. There’s no way to know whether it’s a zoo or a circus by looking
at the data in the table, so both records are suspect. Likewise, it’s hard to tell if
Operation Enduring Angst takes place in Lower Volta or Lower Votla, because the
two records that describe this mission have different spellings.
The circus/zoo inconsistency and the Volta/Votla problem share a common cause.
In both cases the data-entry person (probably a low-ranking civil servant, because
361
C
h
a
p
t
e
r
1
1
D
a
t
a
N
o
r
m
a
l
i
z
a
t
i
o
n
FIGURE 11.1
The badSpy
database schema
looks reasonable
enough.
international spy masters are
far
too busy to do their own data entry) had to type
the same data into the database multiple times. This kind of inconsistency
causes all kinds of problems. Different people choose different abbreviations. You
may see multiple spellings of the same term. Some people simply do not enter
data if it’s too difficult. When this happens, you cannot rely on the data. (Is it a
zoo or a circus?) You also can’t search the data with confidence. (I’ll miss Black-
ford if I look for all operatives in Lower Volta, because he’s listed as being in
Lower
Votla.
) If you look carefully, you notice that I misspelled “sabotage.” It will
be very difficult to find everywhere this word is misspelled and fix them all.
Problem with the Operation Information
There’s another problem with this database. If for some reason Agent Rahab were
dropped from the database (maybe she was a double agent all along), the infor-
mation regarding Operation Raging Dandelion would be deleted along with her
record, because the only place it is stored is as a part of her record. The opera-
tion’s data somehow needs to be stored separately from the agent data.
Problems with Listed Fields
The specialty field brings its own troubles to the database. This field can contain
more than one entity, because spies should be able to do more than one thing.
362
P
H
P
5
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e
B
e
g
i
n
n
e
r
FIGURE 11.2
The badSpy
database after
I added a few
agents.
