Database schema - Configurable fields?

Question

I sell leads and charge my clients like so: (Only one type of payment from the followings can be charged from a client)
Pay Per Lead:
$__ for the first __ leads per month
$__ for the next __ leads per month
$__ for the next __ leads per month
and so on...

Pay per Appointment:
$__ for the first __ leads per month
$__ for the next __ leads per month
$ __ for the next __ leads per month
and so on...

Pay per Percentage of Sale:
__% of the sale price (per sale)

My Question:
What are the best possible database design solutions in such cases?

What i have tried:

+---------+ 
| clients |
+---------+
| id      |
| name    |
+---------+

+---------------+
|     deals     |
+---------------+
| client_id     |
| max_quantity  | 
| cost          |
| unit_type     |
+---------------+

So records for client with the id 1 might look like:

+-----------+--------------+---------------+-------------+
| client_id | max_quantity | cost_per_unit |  unit_type  |
+-----------+--------------+---------------+-------------+
|         1 |           10 |            10 | lead        |
|         1 |           30 |             5 | lead        |
|         1 |          100 |             2 | lead        |
|         1 |           10 |            35 | appointment |
|         1 |           30 |            20 | appointment |
|         1 |          100 |            10 | appointment |
|         1 |         1000 |             5 | appointment |
|         1 |            0 |            50 | sale        |
+-----------+--------------+---------------+-------------+

Now the above table means that:
$10 will be charged per lead upto 10 leads
$5 will be charged per lead upto 30 leads
$2 will be charged per lead upto 100 leads

$35 will be charged per appointment upto 10 leads
$20 will be charged per appointment upto 30 leads
$10 will be charged per appointment upto 100 leads
$5 will be charged per appointment upto 1000 leads

$50 will be charged per sale

Also i want to add x number of such rules (per lead, per appointment, per sale)

I personally don't think that my approach is one of the best solutions. Looking forward to hear for you cleaver folks! Thank you.

P.S. I know that unit_type can be further normalized but this is not the issue :)

Update

Maybe i can store serialized data?

Does "$__ for the first/next __ leads per month" mean "$__ for the first/next __ leads sold to the client in current month"? — dened, Apr 19 '14 at 04:15
@dened No its not per month. Its differential pricing based on quantity. Like $20/lead for first 10 leads then $10/lead for the next 20 leads and so on.. — Lucky Soni, Apr 19 '14 at 05:44
Yes, I see it is differential pricing based on quantity. I ask you what is quantity here? Is it `x`/lead for first/next `y` leads sold to the client in current month? — dened, Apr 19 '14 at 06:30
I'm trying to understand your payment schema, but it is still not quite clear for me... Utilizing your example, how much exactly you charge the client who buy at once 50 leads and have 2 appointments? And if this client buy another 100 leads and have 1 more appointment in the same month, how much you charge him this time? Please describe your calculations. — dened, Apr 20 '14 at 11:16
@dened I am sorry but there will be just one type of payment for each client. I have updated my question to reflect this. — Lucky Soni, Apr 23 '14 at 07:20

score 4 · Accepted Answer · edited May 23 '17 at 12:11

Your proposed schema is a good start and has some merits. IMO the less elegant parts are the denormalized repetition of unit_type values and non-functional max_quantity value for sale.

Would suggest splitting deals into three tables rather than one. Would personally go with singular rather than plural table names** and begin with the same prefix so they are listed close to each other: Something like commission_lead, commission_appointment and commission_sale.

** [Lots of debate on this here]

Would also suggest including both lower and upper bands in each row. This does use more data than is strictly needed but think it is worth doing as it should make the table data more readable and simplify the calculation queries.

So the proposed new schema is:

+---------+ 
| client  |
+---------+
| id      |
| name    |
+---------+

+-----------------+
| commission_lead |
+-----------------+
| client_id       |
| min_quantity    |
| max_quantity    | 
| cost_per_unit   |
+-----------------+

+------------------------+
| commission_appointment |
+------------------------+
| client_id              |
| min_quantity           |
| max_quantity           |
| cost_per_unit          |
+------------------------+

+-----------------+
| commission_sale |
+-----------------+
| client_id       |
| cost_per_unit   |
+-----------------+

And the records for client_id = 1 are:

commission_lead
+-----------+--------------+--------------+---------------+
| client_id | min_quantity | max_quantity | cost_per_unit |
+-----------+--------------+--------------+---------------+
|         1 |            0 |           10 |            10 |
|         1 |           11 |           30 |             5 |
|         1 |           31 |          100 |             2 |
+-----------+--------------+--------------+---------------+

commission_appointment
+-----------+--------------+--------------+---------------+
| client_id | min_quantity | max_quantity | cost_per_unit |
+-----------+--------------+--------------+---------------+
|         1 |            0 |           10 |            35 |
|         1 |           11 |           30 |            20 |
|         1 |           31 |          100 |            10 |
|         1 |          101 |         1000 |             5 |
+-----------+--------------+--------------+---------------+

commission_sale
+-----------+---------------+
| client_id | cost_per_unit |
+-----------+---------------+
|         1 |            50 |
+-----------+---------------+

thank you for taking the time to answer the question. I personally feel that have 3 tables will not be a flexible thing to do. What if i want to add more methods down the road.. will i add more table at that moment? Also the overhead of querying multiple tables? — Lucky Soni, Apr 22 '14 at 07:39
Also i use plural names as its a convention in the framework that i am using :) — Lucky Soni, Apr 22 '14 at 07:41
I wouldn't be scared of the overhead of multiple tables. As long as `client_id` is a foreign key, an RDBMS like MySQL will handle the joins just fine - see [here](http://stackoverflow.com/questions/173726/when-and-why-are-database-joins-expensive#174047). Am proposing this because there is a conceptual separation - the `commission_sale` data in particular doesn't belong in the same table as it doesn't have bandings. — Steve Chambers, Apr 22 '14 at 09:48
I'm not too hung up on the plural vs singular debate but one detail I didn't include is it's good practice to give each table an auto_increment `id` field as a primary key - another interesting read [here](http://stackoverflow.com/questions/621884). I personally like the convention of always naming your foreign key as `<>_id`, which doesn't work so well with plurals. However, as you say it's also nice to stick with the same conventions as your framework. — Steve Chambers, Apr 22 '14 at 09:54
I like this approach. It is configureable at the needs of the Problem Domain. There are systems which use a more abstract concept for specifying configuration parameters in databases (well known is the approach of SAP), but the database design gets poor, unmaintainable and with the exploding of table sizes the performance gets poor, as well. So how would someone use techniques of partitioning using a complete generic database design? It can't be done. ;) — Peter, Apr 25 '14 at 18:48

score 2 · Answer 2 · answered Apr 22 '14 at 14:14

I make an assumption that the change is very rare (update/insert), most of the time you use select to calculate the cost, so I propose this design, the select to calculate cost is very simple

+-----------+--------------+---------------+---------------+--------------+------------+
| client_id | max_quantity | min_quantity  | cost_per_unit | default_cost | unit_type  |
+-----------+--------------+---------------+---------------+--------------+------------+
|         1 |           10 |            0  |           10  |            0 |        lead|
|         1 |           40 |           10  |            5  |          100 |        lead|
|         1 |          140 |           40  |            2  |          250 |        lead|
|         1 |           10 |            0  |           35  |            0 | appointment|
|         1 |           40 |           10  |           20  |          350 | appointment|
|         1 |          140 |           40  |           10  |          950 | appointment|
|         1 |         1140 |          140  |            5  |         1950 | appointment|
|         1 |            0 |            0  |           50  |            0 |        sale|
+-----------+--------------+---------------+---------------+--------------+------------+

select query looks like

select 
   default_cost + ($quantity - min_quantity) * cost_per_unit
from 
   table
where 
   unit_type = $unit_type 
   and (max_quantity >= $quantity or max_quantity = 0)
   and $quantity >= min_quantity

score 1 · Answer 3 · answered Apr 24 '14 at 21:19

IF you consider the cost calculations business logic that is likely to change in the future AND you dont need to filter/sort the table based on the calculation constants, I recommend having one column for rule_id, that pretty much works like your unit_type, and one varchar column called properties where all the specific values needed for that rule is stored with a separator.

You then retrieve the rules that apply for your client to your business logic and do your calculations there. If you need a new rule that suddenly takes 5 parameters, you don't need to change the database schema. Simply write code for a new rule_id in your business logic and you are good to go.

Of course, if you prefer to move calculation logic into stored procedures and/or need to filter or order by rule properties, I think you should go with separate columns for each rule parameter...

Database schema - Configurable fields?

Update

3 Answers3