Design Difficulties

SQL Apprentice Question
I've a project that requires a billing system that has different
requirements for each payment recipient and I was wondering how I'm to
design an efficient DB. Each payment requirement is stipulated in the
contracts w/ each payment recipient.

Some are very easy, just a price per item per month.


One recipient requires that the fee charged be based on different time
frames. E.g.:


1/1/2004 - 12/31/2004 $0.5 per item per month
1/1/2005 - 6/30/2005 $.04 per item per month
7/1/2005 - (until end) $.045 per item per month


Then there are just the flat-fee recipeints: e.g., $5,000.00 per year.


There are recipients that want different fees per area where their
service is given:


Latin America: $0.03 per item per month
UK: $0.05 per item per month
US: $0.06 per item per month


and it goes on like that.


There are those that will receive a different fee base on their ranking
in the market (how many items sold in an area).


We get a report for each area, for each month stating how many items
have been processed.


Do you believe I have to set up an SP that will calculate the payment
for each recipient? And, I'm thinking I'll have to have the data
hard-coded into the SP instead of building many tables to accommodate
the various data structures that would be necessary for each
payment/calculation type - including the necessity of building new
tables/stored procedures for any new contract that requries new payment
criteria.


Any thoughts welcome.


Celko Answers
>>I've a project that requires a billing system that has different requirements for each payment recipient .. Each payment requirement is stipulated in the contracts w/ each payment recipient .. Some are very easy, .. flat-fee recipeints.. recipients that want different fees per area where their service is given .. that will receive a different fee base on their ranking in the market (how many items sold in an area). <<


My first approach, without knowing more than this posting, would be to
create a single payments table with rows that give (recipient_id,
payment_date, payment_amt, payment_method, etc). Write a series of
front end procedure for creating the entire set of rows all at once. I
am assuming that "ranking in the market" takes place at the start of
the contract and not at payment time.

The app then goes to today's work and cuts a check to recipients "WHERE
ayment_date = CURRENT_TIMESTAMP" every morning.


SQL is not a computational language; it works much better with data.



>> I'm thinking I'll have to have the data hard-coded into the SP instead of building many tables to accommodate the various data structures that would be necessary for each payment/calculation type - including the necessity of building new tables/stored procedures for any new contract that requries new payment criteria.<<


hard wired code is not a good idea in SQL; the language is designed to
work with tables.
Think data, not process. Think complete sets that model complete facts
(like all the payments in a contract).