I am creating a payroll system where we put in the the number of days present at the end of the month and it gets added to the database and then we use that to do all the salary calculations.
at present I have one table with all employee details such as name, designation,id etc. but I’ll need to create a separate table for the days present.
The problem is that I am really not sure if making a separate table for attendance is the right way to do this, so I’d love some suggestions from you abut the best way to get attendance for the employees.
e.g. say in january 2017
employee id days attended
873 24
672 27
987 25
and so on.
and then a similar thing for february 2017
but, I am thinking will I have to create a new database table each month then??
what will happen after I give the software to my friend(I am making it for him)
so, what do you recommend I do
what is the best way to do this
so each month the attendance can be put in and it keeps going on and on.
Josh this is good for the presentational aspect of his project, but I think the data is better stored in a row by row “transactional” table where each row represnts one employee for one month. The columns would be “_id”, “employee ID”, “date”, “# of days”. and could run indefinately.
To present the data he would query for the year and display ina table like you have.
Okay, so I’m not clear on this. Are you talking about a database like mongo db and asking how to go about storing that data, or are you asking about a spreadsheet like excel and the best way to organize that data?
Alright, well I can’t be much help with that as I do not have any experience working with mysql. Sorry. I can tell you that using mongodb I would simply create an object doc for each employ like this:
Then it would be easy to just keep adding to this every month. It would also be easy to pull out just the data you wanted as you needed. That’s probably no help tho. I would suggest that you start a new post and make sure you include the fact that your using mysql and want to know the best way to store the data in your DB.
To make it cleaner/clearer about what makes up a pay period, create a table that defines that, with both start and end dates for the period.
Then in your attendance table, you would have employee_id pay_period_id days_attended
This would give you a lot more flexibility if your friend comes back and says, it works but actually everyone gets paid twice a month. Having both start and end dates would make reporting or payroll corrections easier as well.