Payroll Database Structure Question(s)
I'm designing a payroll database and I have some structural questions.
I am thinking of this as somewhat resembling an invoice database... invoicing for time rather than a product, however there are a couple of riddles that I can't seem to solve.
FYI 1) I want to have an input screen for a person. This information is "everything" about the person.
FYI 2) There should be a way to generate a list of all active employees... which the reason I mention this is because of riddle 1).
FYI 3) I want to keep my 'invoices' to see what was submitted week after week
Riddle 1) I want to be able to create an invoice that auto-generates a list of employees. The reason for this is because I have the same employees every week. A portal would be cool... but I don't want to have to enter them in like I would in a typical invoice database (i.e., line items). I just want the current employees to show up... currently they are considered 'active' by the combination of two fields.
Riddle 2) All employees will be entered via the same input screen, but some employees are salaried, and some are hourly. I currently have a field that denotes this. The issue is that I will enter an hourly employee into the system every term (I work at a university) and "archive" old information... thus some employees could have 30 records attributed to them in the initial "People" database (differentiated via a combination of Record - File - and Employee # fields. I need to grab only the current information. How can this information be auto updated in my 'invoice' screen?
Does this make sense?
If not, please let me know and I'll try to explain better.... :)
How on earth do I go about structuring this... I'm thinking that some scripting is involved, but I'm getting confused on how many tables are necessary, how they relate and get copied an auto-generate. !!! I have looked at invoicing databases... but I still feel lost.
Thank in advance.