Link Tables

[George]

I am now working with old DB that they want a new table
for:

Old: Contracts Table
Contract number
Company name, address, phone, contacts....
City of Contract

Fees Table
Contract number
Company name
City of Contract
124 fields of different Fee Amt

They want a table with 124 Fee codes (One for each Fee Amt)
to use as a pick list of what was charged to a Contract
number by Company by City

Could have more than one Contract number for a company for
a city - But different Fee Amt

HELP!

 

[Tim Ferguson]

Fees Table
Contract number
Company name
City of Contract
124 fields of different Fee Amt

Surely the Company Name belongs in the Companies table, or at the very
least in the Contracts table? And the City definitely belongs somewhere
else!

They want a table with 124 Fee codes (One for each Fee Amt)
to use as a pick list of what was charged to a Contract
number by Company by City

Yes you are quite right: this is bad, bad, bad.

Could have more than one Contract number for a company for
a city - But different Fee Amt

Correct: you need a new table of FeeAmounts, looking like this:

*ContractNumber
*FeeType
Amount
HasBeenPaid
ChequeOrInvoice
etc...

The * represents the joint Primary Key. You might need a table of
ValidFeeTypes to control the FeeType field. Summing and checking fee
amounts would be a real pain with the original design: easy in the
relational version.



Hope that helps


Tim F