Tài liệu Java Database Programming Bible- P2 ppt

Chapter 2:Designing a Database

-49-

In practice, you are unlikely to encounter a problem with BCNF, since the purpose of

assigning a unique ID column rather than relying on supposedly unique legacy data is

to prevent problems of this sort.

Law firm data

Having created the tables required to manage the clients, you can move on to setting

up the tables for the law firm itself. However, after a moment's thought, you will

probably realize that the tables you have created will handle all the data for the law

firm, too.

Billable items

In a time and materials invoicing system, there are two kinds of billable items: fees

and expenses. Fees are charged in a number of different ways, the most common of

which is hourly. Expenses are simply charged on a unit basis, as in the case of photo

copies, which are billed per page copied. In either case, the id of the law firm

employee, or timekeeper, making the charge is provided.

The first table required for billable items, then, is the Timekeeper Table. This table

includes a foreign key identifying the individual in the Contacts Table, as well as

columns for level and hourly rate. The LEDES specification defines the following

levels:

ß Partner

ß Associate

ß Paralegal

ß Legal Assistant

ß Secretary

ß Clerk

ß Other

These levels are best stored in a Lookup Table of billing levels, accessed by a foreign

key in the Timekeeper Table. Hourly rates, too, should be stored in a Lookup Table,

to allow for increases. These two tables contain only an id column and a

corresponding level or billing rate, so they are not shown here. The resulting

Timekeeper Table might look like Table 2-4.

Table 2-4: Timekeeper Table

id contact_id level_code default_rate_code

1000 2001 1 1

1001 2002 1 2

1002 2007 5 9

TEAMFLY

Team-Fly®

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Chapter 2:Designing a Database

-50-

Notice how this structure allows for two partners to bill at different rates. It is also

intended that the rate code be overridden if the terms of a contract require it.

The billable items are stored in a table that contains the date, a reference to the

matter or project, and the id of the timekeeper, as well as information about the

specific activity being billed. I have called the table Billable Items, as it is structured

such that expense items can be inserted as easily as billable hours.

The Billable_Items Table shown in Table 2-5 contains foreign keys linking it to the

Timekeeper Table and the Client_Matter table, as shown in Figure 2-2.

Figure 2-2: The Billable_Items table is linked to the Client_Matter and Timekeeper tables.

Table 2-5: Billable Items Table

id date matter_id tk_id task_code activity_code units rate_code description

1 4/12/02 7001 2002 L530 E112 300 0 Court fees

2 4/12/02 7001 2002 L110 A101 2.5 1 Review File

The task and activity columns refer to the industry standard Litigation Code Set

developed by the American Bar Association, the American Corporate Counsel

Association, and a sponsoring group of major corporate law departments. A copy of

the Litigation Code Set can be purchased from the ABA Member Services

Department, or viewed on line at:

http://http://www.abanet.org/litigation/litnews/practice/utbms.pdf

In the example of Table 2-5, E112 is the Litigation Code Set code for court fees, while

the rate code 0 is used to handle fixed-cost items, as opposed to items billed on a

per-unit basis. This permits the merging of unit billings with fixed cost billings without

introducing additional columns to handle them separately.

If you add an extra column to handle fixed-cost billings, you introduce a possible

ambiguity, because it becomes possible to enter both fixed and unit billings in a single

row. This violates the requirements of the fourth normal form because it creates

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Chapter 2:Designing a Database

-51 -

nonmeaningful combinations of column values. By handling the situation through the

rate code, you can use just one table, conforming to the requirements of the fourth

normal form.

The tables also meet the requirements of the fifth normal form, which are as follows:

ß The table must be in fourth normal form.

ß It must be impossible to break down a table into smaller tables unless those tables logically have

the same primary key as the original.

By separating address information into a table separate from the Contacts and

Clients tables, you can see that if this separation is necessary to conform to the fifth

normal form. The addresses do not logically share the same primary key as either

contacts or clients.

Matter or Project Tables

Having designed the simpler tables, it is time to move on to handling the Client Matter,

or Project, Tables. These tables encapsulate the information specific to the service

the law firm is performing for the client. As such, they contain the following:

ß Matter Data

ß Name

ß Client reference number

ß Law firm reference number

ß Law firm managing contact

ß Law firm billing contact

ß Client primary contact

ß Billing Data

ß Billing type

ß Electronic funds transfer agreement number

ß Tax rate information

ß Fee sharing information

ß Discount agreements information

ß Invoice currency and payment terms

ß Invoice Data

ß Date

ß Due date

ß Amount

ß Staffing

The Matter Table and Billing Rates Table are separate; in an ongoing relationship

with a client, a law firm may establish a billing agreement that applies to a number of

individual matters, so billing data is not strictly specific to a single matter. Conversely,

a billing agreement may be renegotiated during the life of a matter.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Chapter 2:Designing a Database

-52-

The Client Matter Table illustrated in Table 2-6 contains the columns billing_cid and

client_cid, which are foreign keys pointing to entries in the contacts table, and are

labeled with a _cid suffix to denote contact_id in order to avoid confusion with

client_id.

Table 2-6: Client Matter Table

id client_i

d

client_re

f

nam

e

billing_rat

e

manager_i

d

billing_ci

d

client_ci

d

1000

1

1201 ref-3711 Jones

v

Biddle

2 1004 1007 2001

1000

2

1296 b7997 Jones

v

Biddle

1 1001 1007 2093

The Billing Rates Table shown in Table 2-7 includes a type code that simply points to

a Lookup Table of billing types, including the following:

ß Time and Materials

ß Flat Fee

ß Contingency

ß Fee Sharing

Table 2-7: Billing Rates Table

id type_code discount_type discount tax_rate_fees tax_rate_exp terms

1 1 1 15 5 5 1

2 1 1 12.5 5 5 3

Discount types is also a reference to a Lookup Table containing the entries FLAT and

PERCENT. Based on the selected discount type, the discount contains either a flat

discount amount or a percentage discount rate. The terms column contains another

lookup code pointing to a table of payment terms such as 10/30, which means that

the billing firm accepts a 10 percent discount if the invoice is paid in full within 30

days.

Generating an Invoice

Generating an invoice involves retrieving a list of all open matters and summarizing

the billable items outstanding against each open matter. For the purposes of this

example, a Dedicated Billings Table will be created. This table has a one-to-one

relationship with the Client Matter Table, as shown in Figure 2-4.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Chapter 2:Designing a Database

-53-

The process involved in creating an invoice is to scan the Billings Table for matters

where the status indicates that the matter is still open. (When a client matter has been

resolved, and the final invoice paid, the status is set to indicate that the matter is

closed.) The links between the tables are shown in Figure 2-3.

Figure 2-3: Invoices are generated by creating a list of billable items which have not been

previously invoiced.

The next step is to compare the Invoiced_Items Table against the Billable_Items

Table to find items associated with an open Client_Matter that have not been invoiced.

Items that have not been invoiced are added to the Invoiced_Items Table, with their

Invoice_ID set to indicate which invoice they were billed on. The Invoiced_Items

Table is shown in Table 2-8.

Table 2-8: Invoiced Items Table

id matter_id item_id invoice_id

10001 2006 2031 1007

10007 2119 2047 1063

Another way to handle this is to add an Invoice_Id column to the Billable_Items Table.

The Invoice_Id is then updated when the item is invoiced. The advantage of this

approach is that you are not adding a new table with a one-to-one relationship with an

existing table. The disadvantage is that updating a table can be slow compared to

adding a new row.

Table 2-9 shows the Invoice Table. The Invoice Number column provides a legacy

system compatible invoice number, and the start date and end date columns identify

the billing period covered by the invoice. The Billing Rate Id column is a foreign key

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.