Employee Attendance Design.

Question

so my company of 3ppl is expanding (:O) and I use to keep the hours done by each of us on a simple Access table with just start and end time.

Now that I  have a secretary and a few other workers I'm going to need a more accurate databse for employee attendance.

My simple concept is this:

Employee(EmpID(PK),Fname,LName,UserName)

Login(UserName(FK), password)

Attendance(AttendanceDate(PK),EmployeeID(FK),StartTime,EndTime)

I'm going to make the secretary insert the hours so I want to give each user different sets of permissions. Do I make a table and then reference the table when a user tries to access something?

UserAccess(UserName(FK),TableName,Read,Insert,Update,Delete)

so say USer=bob, table=attendance,read=yes,insert=no,update=yes)

how would I check this table with that the user is trying to do I guess... I don't know much about permissions in access(or anyDB)

Another thing I thought about but I have no clue how to approach is double sign ins.

circumstances:

1) user signs in, not sure if they did or not so signed in again (same for sing out)[newest one take presedence?)

2)signs in to start, signs out for break, sign in for break, sign out for lunch, sign in from lunch, sign out for home time

How would one go about to keeping a log for all entries of each day...

I'm probably going to remake the DB in mysql express but I need to get the design down for these circumstances.

Any design help would be greate:)

Answer 1

I wanted to make an app for my tablet for it. I think it would be a fun side project aslong as i can figure out the 2 circumstances..

figured for the 1 circumstance I can just check to see it user signed out yet, if no, replace current sign in time with previous.

still no clue for multiple entries in one day tho

Answer 2

Any update on this?

Thanks!

---

Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Twitter, Wiki)

Answer 3

Hello Kokoroastrum,

You need to have a consultancy session :)

About your access/permission query:

- You can have only one table & have different levels of permission on those.

- There is no need to have tables as per users.

About your circumstance 2:

- Attendance table can have AttendanceDate, EmployeeID, Time, Comment.

- I see that you have a PK for AttendanceDate which fail because Emp1 & Emp2 can come on the same day.

- Comment field can have details about break/lunch. You can have a constraint on Comment field to accept particular values which can be Sign In/Sign In Home/Lunch/Break/Home....

- Example records will look like:

01-Jan-2013, Emp001, 13:00, Sign-Out-Lunch

01-Jan-2013, Emp001, 14:00, Sign-In-PostLunch

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.