Designing a Database - Java Database Programming Bible - page 42

Table 2 -2 raises the question of handling data items that may also be stored
elsewhere. For example, the partners may have their own fax lines, although other
employees do not. An obvious way to handle this is to add a fax column to the
Contacts Table. However, you should use this column only for personal fax numbers
and should set it to NULL for employees who do not have their own fax numbers. You
can then write your queries to return the shared fax number if no personal fax number
is found. If you fail to do this and you insert the common fax number for each
employee, you will be duplicating data.

Table 2-2: Contacts Table

id fname lname company_i

d

address
_ id

email phone cell

100
1

Oliver Dewey 1001 1004 [e-mail] 555-123-456

7

444-123-333
3

100
2

Ichabo
d

Cheat-ha
m

1001 1004 [e-mail]

m

555-123-456
8

444-123-333
4

100
3

Anne Howe 1001 1004 [e-mail] 555-123-456

9

444-123-333
5

Having dealt with the lower levels of client data, you are now ready to create the
Client Table itself. This has now become rather simple, since all it needs to contain is
the client name, tax id, and address id, as illustrated in Table 2-3 .

Table 2-3: Client Table

ID FIRM_ID NAME ADDRESS_ID TAX_ID

2001 cl0536 Acme Insurance 1001 45-6789012

2002 cl7324 Clark Plumbing 1002 52-6783716

The Firm_ID column is shown her to illustrate the kinds of apparently extraneous
information you can expect to find when working with any legacy system. LEDES
includes the Firm_ID field as a concession to legacy accounting systems their
members are using. Many of these systems contain data fields which may not be
pertinent to the needs of the LEDES system, but which are significant to the member
firm.

The relationships among these tables is shown in Figure 2-1 . The address_id
columns in the Client and Contact Tables are foreign keys linking them to the primary
key in Address_info.