I am still designing an HR system, and I have encountered a couple of interesting issues:
1. I need to save a few constants in my DB. For example, the company pays each employee extra X% of his/her wages for pension. X is a constant, and doesn't need to be inserted every time a new employee is inserted. However, the users might want to change it one day to X+1 or any other new value. I have several constants like this, how should I handle them ?
2. Leaves: for example, every employee gets 15 days of leave a year. I need to store this constant for every employee, but to update it when he/she goes on holiday. When a new year start, it will need to be 15 again...
It's not really a constant if it can change, is it? Let's call them parameters instead.
1. I've seen designs that use a single table with a column per parameter, and a hardcoded primary key like 1 (or worse, no PK and assuming a single row or using LIMIT 1). Another technique uses a table per parameter type, indexed with a string key.
It might be more useful to give each parameter its own table, using the year or effective date as primary key.
2. You could record the total days per employee, and used days per employee and year.