I'm building an database to manage paying some of our freelance sellers. They get a daily rate, commission per product, and other expenses (such as mileage for example).
Some of the items and expenses have standard rates. For example, £20 for selling a certain type of item. The will of course change over time. So, for example, this year you sell machine X and you get £20. Next year you'll get £22. I can construct a model for the normalised data. However, I'm not sure how to handle the dreaded 'other' categories that aren't in the database and would need to added to the report when they submit what they sold and their costs. For example, perhaps they had to hire a car at the last minute. This isn't in the expenses database so they need to put down, "car hire", how many days and how much per day. Or maybe they had to pay for a translator or something. So I'd need some way of capturing their description, the unit rate and the units.
- Would I have some additional 'other stuff' table that they could add to and then do a union with the normalised data?
- Would I change the base structure to somehow accommodate these exceptions?
(Sorry ... I'd like to get it right and I'm sure this is the sort of thing that people experience all the time. It's always those 5% events that take 95% of the time.)
Types
.id
.description
(eg products, expenses ...)
Items
.id
.type_id
.description
(eg Laptop 2000, Supermouse, Mileage...)
Rates
.id
.item_id
.date_from
.date_to
.unit_rate
Invoice
.id
.rate_id
.event_id
.units
Other_expences
.id
.event_id
.description
.units
.unit_rate