Creating payroll application - problem with attendance


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.

what is the best way to do this?

I am maybe just misunderstanding your question, but could you just create a column for each month, and then store the whole year in the same DB?

    <th>Emp ID#</th>


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.

Maybe that’s what you are saying as well.

if I create for a whole year then I’ll have to update the application every year and add in a new year in a new table. or am I missing something?

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?

I am talking about the database like mongodb.
it’s actually mysql but it’s a database, not an excel sheet.

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:

id: string,
firstName: string,
lastName: string,
dateOfHire: string,
{ year: Number, month:Number, hours: Number },

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.