Database schema - Configurable fields?
database, database-design, mysql, relational-database
Solution
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 |
+-----------+---------------+
Problem
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?