User specific records

[madman411]

Hi all

Firstly, apologies if this is in the wrong sub-forum. I wasn't able to determine which part of the forum this belonged.

I have a small company that has a couple different departments within it. I'm wanting to create a system that allows employees to enter their worked hours each day prior to payroll processing their timecards separately. This is a request from management. No taxes and such are calculated within this, this is more of a daily/weekly review to track overtime and hours worked. They call them "production reports". They use these when approving or flagging payroll timecards.

Ideally:

- each user login has their own "profile" layout where they can see their department and create their timecard entries for review.
- each user can only access/create their own respective timecard records.
- department managers can see their own timecards and the timecards of people within their department. The manager can also edit them if it's necessary.
- the administrator can select/view/modify all records and grant users managerial access rights, add new staff, generate reports for payroll, etc.

I have never worked with user-specific records before, but have limited experience with access rights. Typically my databases that have been deployed have about two users on average, so this scale of a solution is new to me. Basically my question here is what is the best way to structure such a database?

1) a table that contains users
2) a table that contains departments
3) a table that contains work weeks
4) a table that contains daily timecard entries

What is a recommended way to dynamically adjust what records are available to what user? For example, a day-to-day employee is promoted to manager and needs their privilege set changing. Is a hidden field within the record that contains the employee and manager's unique ID an option so FileMaker can permit or deny access accordingly? Some employees are affiliated with a union, so their respective overtime schedule needs to be applicable, but I believe I can achieve this through calculations.

Initially, buttoned up security isn't a huge concern, but obviously I would like to build this database with it in mind once it's approved and deployed. I'm also told this may be moved to a web-based service rather than their internal FileMaker system.

Thank you!

Edited 14 hours ago by madman411