Java Reference
In-Depth Information
According to a widely quoted remark commonly attributed to C.J. Date, one of the
gurus of the relational database world, the primary principles of database design are
"nothing more than formalized common sense." Or, as David Adams and Dan Beckett
express it in their book
Programming 4th Dimension: The Ultimate Guide
: "The
purpose of formal normalization is to ensure that your common sense and intuition
are applied consistently to the entire database design." Since
Chapter 1
discusses
database design from a theoretical viewpoint, this chapter uses a common-sense
approach and ties the results back to the rules of normalization.
See
Chapter 1
for a discussion of normalization.
Cross-Reference
Client and contact data
The obvious first step is to design a Client Table. At first glance, it looks as if you can
do this by simply mapping the relevant portion of the XML of
Listing 2 -1
to a table.
However, bear in mind the following considerations:
The client is frequently a corporation, represented by an individual or individuals involved in a
specific matter.
The client company may often assign different employees to handle different aspects of a given
matter.
Each individual may have a different phone number, mail drop, or cell phone, but all may have
the same mailing address.
A corporation may operate out of a number of different locations.
Since one of the guiding principles of database design is to avoid storing the same
item of information in two or more places, these considerations mean that the
information about a client has to be divided into a number of different tables. The best
place to start is with the lowest level of data, in this case the address. Addresses are
stored in a table by themselves, separate from, but linked to, the clients or individuals
by a foreign key in the Client or Contact Tables (see
Table 2 -1
).
Table 2-1: Address_Info Table
id
address_1
city
state
zip
country
phone
fax
1004
123 Penny
Lane
New
York
NY
1006
USA
555 - 123
- 4670
555 - 123
- 4690
1005
711 Quarter
St
New
York
NY
1007
USA
555 - 119
- 3232
555 - 119
- 3239
The next level of information concerning a client is the contact person. This table uses
a foreign key, address_id, to link to an address. It also contains individual phone
numbers and e-mail addresses.
