none
How Do I Deisgn a PayRoll Database

    Question

  • I have been struggling with the structure of Payroll DB without luck. In my corner of Africa, Salaries are paid at month-end.

    I attempted something like this -over simplified here:

    Table1: EmployeeRecords:

    EmpName
    Dept
    DateOfFirstAppointment
    SalaryScale

    Table2: SalaryStructure

    SalaryScale  ...int
    Salary....money

    I can then join Table1 and Table2 on SalaryScale to determine monthly salary of each staff. My problem is how to keep a monthly record of the salary payments so that in future I can retrieve the data for any previous month.

    Any clue, any one please?

    Only performance counts!
    Thursday, January 22, 2009 7:26 PM

Answers

  • I suggest that you may find this Access database template to be a good start. Your current 'model' is missing the actual 'Payroll' table (and the other tables are missing necessary fields.).

    Try:
    http://office.microsoft.com/en-us/templates/TC102220941033.aspx?CategoryID=CT101428241033

     


    You may be only one person in the world, but you may also be the world to one person.
    Friday, January 23, 2009 8:58 AM
  •  Table: PayrollPayments

    • PaymentId          (Could be IDENTITY)
    • StaffId            (FKey to Staff table)
      PayPeriod          (like: 20090101-20090131)
    • PayDate            (Date Salary is Paid)
    • SalaryDue          (Actual Amount from SS Table -NO link)
    • Deduction1         (Deductions to pay)
    • Deduction2
    • Deduction3
    • Deduction4
    • AmountPaid         (Total Amount Actually Paid)
    • HowPaid            (Cash, Check, EFT, etc.)
    • PmtIdentifier      (Check Number, EFT number, etc.)

    The primary key could be ( StaffId, PayPeriod ).

    This is a very 'rough' guess of what would be useful information in your situation.

    Having BOTH PayDate AND PayPeriod will allow easy accomodation to weekly or bi-weekly payroll -and protect from duplication, accidental or intentional.

     (Where are you in Africa?)

     


    You may be only one person in the world, but you may also be the world to one person.
    Saturday, January 24, 2009 10:32 PM

All replies

  • I would add a Key to the employee table. You need something unique - say you get 2 people with the same name. Need to be able to tell them apart.
    Payments would have this key, the date, and the amount paid at a minimum.
    You might want to see if any of the Adventure Works samples have similar structure. The samples can be very good to learn from.
    There is a lot more to payroll than amount paid - taxes, insurance, retirement accounts, etc.
    Finding a sample database, with multiple tables, indexes, and constraints will help you see how this fits together.
    You also may want to have some way to keep revisions. Names for example can change over time.
    Try to learn more about the relationships - proper database design can make things much easier to maintain. And adding things later is also easier.
    Greg E
    Thursday, January 22, 2009 11:13 PM
  • I suggest that you may find this Access database template to be a good start. Your current 'model' is missing the actual 'Payroll' table (and the other tables are missing necessary fields.).

    Try:
    http://office.microsoft.com/en-us/templates/TC102220941033.aspx?CategoryID=CT101428241033

     


    You may be only one person in the world, but you may also be the world to one person.
    Friday, January 23, 2009 8:58 AM
  • Thanks all for your response.

    I have checked the Time Card Template and it is NOT what I am looking for. Pls see my original post/question: In my corner of the world, salaries are paid monthly and not hourly. It is usualy paid to everyone in the Nominal Roll as at month who is qualified to be paid irrespective of the number of hours worked. In fact, no record is kept of the number of hours worked, per se. So we can ignore that aspect of the database.

    I know that my sample tables are not adequate. I only wanted to make things simple and save my time and yours. Like I said, it is working ok as it is except that I cannot keep record of monthly payments because I do not know how to capture that in my DB.

    Could any one with experience of similar payroll system pls assist.

     

     


    Only performance counts!
    Saturday, January 24, 2009 7:39 PM
  • Sylva,

    The difficulty is that it would be necessary for you to provide a large amount of information for us to help you design a payroll system. So we may offer ideas, and perhaps links to 'models' -expecting that you will examine those suggestions and/or 'models' and determine if htere are components that are useful in your situation.

    In the most 'base' form, you need:

    • a table of Employee information,
    • a table of Salary information (including the 'effective' date),
    • a table of Payments (for historical purpuses)

    You would JOIN the Employee data with the Salary data to determine the current effective pay, and then store that data in the Payments table.

    And, of course, there may be many reasons to have additional tables to handle other business and requlatory requirements related to payroll.

    Here is another source of a data model for your review. Remember, no one model is likely to exactly suit your needs -you have to engage your own creativity in order to extrapolate the useful components.

         http://www.databaseanswers.org/data_models/payrolls/index.htm

    You may find this thread also useful...

         http://www.access-programmers.co.uk/forums/showthread.php?t=113240 

    A web search on the terms "payroll data model" might be fruitful. ;-)

     

     


    You may be only one person in the world, but you may also be the world to one person.
    Saturday, January 24, 2009 8:06 PM
  • Thanks Rowland. I will check the links but let me try to explain things further. Assume that Sylva Africa Corp. has 1250 staff members each on varying salary scales. Lets assume that there are only three salary scales (SS): SS1  --- $10,000.00 monthly, SS2-----15,0000.00 Monthly, SS3 --- $20,000.00 monthly. That means a one (SS) to many (Staff) between the Staff Table and SS table. Presently, manual payrolls are prepared monthly to handle salary payments. To do this, a large Payroll exercise book is used which is prepared month by month.

    Sylva Africa Corp requires a Payroll DB to track the salary payments for one year. I can identify two tables: Staff Table and SS table and they will look like this (over-simplified here, please):

    StaffTble:

    StaffID  ..... int PK

    FirstName.....varchar(15)

    LastName ....varchar(15)

    HireDate .....smalldatetime

    SSID ---- int FK references SStbl

     

    SSTbl:

    SSID .... int PK

    MonthlySalary ...money

     

    By joining the StaffTbl with SStbl on SSID, I can generate monthly payroll (assuming no changes within the year). My problem is: How do I maitain a complete database of one-year salary for all the staff.

    Ignore taxes, deductions and other adjustments usually associated with payrolls.

     

    I hope that this makes things clearer. With sincere appreciations.

     


    Only performance counts!
    Saturday, January 24, 2009 8:37 PM
  • You are still missing the point.

    Payroll is very complicated and requires a robust data model.

    In the scenario that you present, there are problems in determining the proper payroll for any employee that received a Salary Scale change during the course of a year.

    Yes, the link SSId will help, but when is the salary change effective? What happens if it is changed and a previous salary must be recalculated? How do you reconstruct the salary history, including dates, times, and who made and who authorized the salary change?

    You really 'should' take the time to educate yourself on payroll data models before you create a situation that will cause future problems. You are not going to the the 'correct' answer here. I've tried to guide you toward resources that you can use to learn from and adapt to your needs.

    Perhaps some of the complete payroll software packages available would be a better option for you since the designers may have spent the time creating a robust design and you don't seem to want to invest the effort. I'm sure that there are 'free' and/or 'shareware' payroll products available.


    You may be only one person in the world, but you may also be the world to one person.
    Saturday, January 24, 2009 9:05 PM
  •  Sir, I said that we should ignore adjustments/authorizations and ALL other complexities of a payroll. My only problem is how to keep/store/maintain record/database of previous salary payments.

    I can take care of all else (or, they are not my problems for now).

    I wll appreciate any clues in that direction, please.


    Only performance counts!
    Saturday, January 24, 2009 9:17 PM
  • Then it is simple.

    All you have to do is create a table that contains the payroll information that you wish to save each pay period, and add the payroll data to that table when the payment is made.


    You may be only one person in the world, but you may also be the world to one person.
    Saturday, January 24, 2009 9:22 PM
  • But that's exactly my problem. How do I do that? It seems that what is missing is a paydate colunm but I have no idea where and how to create it.
    Only performance counts!
    Saturday, January 24, 2009 9:28 PM
  • As I indicated in a previous post, you need at least one additional table. In that table, add any columns (including PayDate) you need to store the desired data. 

    Arnie Rowland said:

    In the most 'base' form, you need:

    • a table of Employee information,
    • a table of Salary information (including the 'effective' date),
    • a table of Payments (for historical purpuses)

    You may be only one person in the world, but you may also be the world to one person.
    Saturday, January 24, 2009 9:32 PM
  • I guess we are very close to the solution. Pls forgive me for the long exchange. Could you please give me some idea of the third table with the paydate because I have been thinking about this for over a year. Maybe a simple table with a few colunms showing how to relate it (the third table) to any of the previous tables will do the magic.

    Please note that all employees are paid on the same date and that barring any adjustments, the same data is generated month-after-month for the entire 12-months.

    Million thanks.

     


    Only performance counts!
    Saturday, January 24, 2009 9:40 PM
  •  Table: PayrollPayments

    • PaymentId          (Could be IDENTITY)
    • StaffId            (FKey to Staff table)
      PayPeriod          (like: 20090101-20090131)
    • PayDate            (Date Salary is Paid)
    • SalaryDue          (Actual Amount from SS Table -NO link)
    • Deduction1         (Deductions to pay)
    • Deduction2
    • Deduction3
    • Deduction4
    • AmountPaid         (Total Amount Actually Paid)
    • HowPaid            (Cash, Check, EFT, etc.)
    • PmtIdentifier      (Check Number, EFT number, etc.)

    The primary key could be ( StaffId, PayPeriod ).

    This is a very 'rough' guess of what would be useful information in your situation.

    Having BOTH PayDate AND PayPeriod will allow easy accomodation to weekly or bi-weekly payroll -and protect from duplication, accidental or intentional.

     (Where are you in Africa?)

     


    You may be only one person in the world, but you may also be the world to one person.
    Saturday, January 24, 2009 10:32 PM
  • Thanks. I will try it out and send feedback.

    I am in Nigeria, West Africa. By the way any clues on where I can get practical 4-weeks training on Database programming? Been learning on my own though I am a Chartered Accountant.

    I am very grateful. Have a great sunday.


    Only performance counts!
    Sunday, January 25, 2009 5:22 AM
  • Ah, you are from Nigeria. Please tell General Abacha that I am still waiting to help transfer his money out of those bank accounts...

    You may find some of these on-line training materials to be useful.

    SQL Server Learning Materials and Tutorials
    http://code.msdn.microsoft.com/SQLTutorials


    You may be only one person in the world, but you may also be the world to one person.
    Sunday, January 25, 2009 8:38 AM
  • No wahala (problem), my brother. Not all of us transfer General Abacha's money; but if you are interested you can contact Mr. Bernard Lawrence Madoff please.

    Is there no training school that offer short-time (say, 3 to 4 weeks) class room tutorials on SQL Server/VB .NET in Yankee land?

    I am exceedingly grateful.


    Only performance counts!
    Sunday, January 25, 2009 6:58 PM
  • Perhaps General Abacha was a student of Mr Madoff...

    It is difficult to advise you about training. There as so many excellent training vendors.

    You can do a web search for "SQL Server" AND training. You may also wish to use "boot camp". Be forewarmed that the 'boot camp' experience is a compressed form specifically oriented toward passing the certification exams. A better option may be some of the intensive courses held by various Colleges and Universities.

    You should expect a cost of ~$8000 (US) for a four week course, plus housing/meals, and transportation.

    Your closest training facilities may be in South Africa, or depending upon flights, even the London area.

    Good luck and success with your continued exploration of, and learning about, SQL Server.


    You may be only one person in the world, but you may also be the world to one person.
    Sunday, January 25, 2009 7:37 PM