When to create a new table?

Question

I have an application with employees and their work contracts. Each work contract has a Vacation. We keep track of how many days the company owes the employee ("VacationOwe") and how many he has taken ("VacationTaken").

Should I make this "VacationOwe" and "VacationTaken" a property of the Contract, or should I create a class (table) called Vacaction with the properties "VacationOwe", "VacationTaken" and "ContractId" and join the two tables?

What are the advantange of both methods?
Is there any rule when you should create a new class or table or keep the data in one.

Answer 1

If the two properties are truly only related to an employee, there's no benefit of creating a separate table. Performance will be worse and you'll constantly have to join on those tables.

For this specific example, it seems like the vacation days may also be linked to a year. If that's the case, a separate table would make sense so you can track vacation days taken/owed by employee and year.

Answer 2

should I make this "VacationOwe" and "VacationTaken" a propertie o the employee

Most probably...No. Because this defeats normalization. Also, it does not provide information about when the vacations were taken (in case you care about that). In addition to that, you have to do this calculation for every employee, every year.

Use a class (table) Call Vacaction with properties "VacationOwe" and "VacationTaken" and "EmployeeId" and cross the two tables?

It is not good to have a singleton class in this case. In general you should avoid singletons in most cases.

So what to do? Well, if you system does not care about vacation details, you could go with the first solution. Maybe you want to consider option (A) below or If you would like to have a more generic approach you could do something similar to option (B). It all depends on your detailed requirements.