I need to implement a database. for employees. The customer wants to be able to select a employee from the datagrid (already implemented), and enter the months that they want to see there gross salary. So for example, they might want to see i employees salary from Mar to Sep. and print this in a report.
The problem, how do l set the relationships in the database. I have one table Employee, and the other one Month. The foreign key month is in the employee table. In the month table l wanted to list every employee under each month in the month table, and to do this for all the months. But the problem is, that you cannot, as you will have duplicate entries in the employee number primary key.
I was going to have each month as a table of its own, but that would mean creating 12 tables. But that would not work either.
I hope someone could help me with this problem. If you can understand my problems.
Have one table Employees which among other contains one primary key field EmployeeID (or such).
Have one table Salaries which has one primary key ID (AutoNumber for instance) and fields EmployeeID, Month, Salary (and others).
The relationship is a one-to-many between tables Employees and Salaries via the EmployeeID field.