Hi
As promised i am back again from the beginning of my project, attached is tables and relationships that i have made to start this project, please i required your kind help at my database start up version for verifying tables design and its relationship.
All is done already by me now need your views/inputs and suggestion if its required to change any fields or details in attached database.
The purpose of building this database to monitor/report with overall employees records and related stuff.
This database will track and calculate employees usage, benefits & records and in same time it will generate many reports but this will come in my project's next step for now i need to focus on tables and their relationships.
tblAccessLevel
This to give users with access level once they login different users will have different login writes (This table work will come later) need to see the relationship and fields are mentioned correctly.
tblAirtickets
This table is recording the air tickets being issued for employees for various purposes
tblBankDetails
This table is record set for employees bank details where all employees bank account details will be stored.
tblBonus
This is to record the bonuses paid to employees with amount and date of payment
tblCalendar
This is to monitor the weekends and working days as well as holidays for previous years and current changes this is as per the gulf region so i have to tailor it as per hijiri calendar also that not the issue now just for your information.
tblContacts
This is employees contact details Local & Home Country.
TblDepartments
This is departments within the operation area and each employee is belongs to any of this department, I have not completed the allocation but just need to see all relationship and field names are ok
tblDependance
This table holds the details for employee’s family cost and categories that company paying for behalf of employee based on their employment contracts just as Medical & other Government fees for residentID etc.
tblEmployeeEvaluation
This table remains for employees evaluation before paying for (Bonus & Increments) so I have linked this to both tables.
tblEmployees
This table holds employee personal details and employee list with their EmpID as crucial in this database.
tblEmploymentContracts
This table have information about employees contractual agreements and writes and its linked to tblEmployees with PK many calculations will be done based on their writes and data mentioned in this table.
tblEndofService
This table hold the information and records for employees gratuity payments if either its paid by partial or full in one or many payment for single employee.
tblIncreaments
This table hold the information for increments being given to each employee with amount and date of payment.
tblEvaluationPoints
This table to link it with tblEmployeeEvaluation to show the performance points for each category of performance selected for employee and based on this points will be add/subtracted from employees total points.
tblInsuranceProviders
This piece of information show how many insurance provider we have for different category of insurance and could be added more and amended existing.
tblJobTitles
This is list of job titles that represent the job nature and responsibility.
tblWorkPermits
This is kind of work permit details that is linked directly to resident ID so each resident have one work permit and its details are mentioned in this table with fees and renewal terms etc.
tblLifeInsurance
This is employee’s life insurance details and insured amount etc.
tblMedical
This is employee’s medical insurance details and insured amount etc.
tblNationality
This table show all nationalities to select for employee and other requirement of nationality identification.
tblOvertime
This table record the overtime for employee with reason and approval etc
tblPassport
This table to hold the details for employee’s passport validations, this should be linked to ResidentID and employee because employee holds the passport and ResidentID both.
tblPayroll
This table is record of payroll for employees each employee each month have one line with his pay details and also its design for different payroll cycles as per the future requirement.
tblQualifications
This table record the qualification of employees with highest study in employees qualification list one record per employee only.
tblQuestions
This table hold the security questions for Users who use to login in this database this will come in next step just need to see the relationship and field are correct.
tblResidentID
This table have information for employees resident ID and its validations
tblSickleave
This table have sick leave taken and approved, paid for different categories such as maternity and general sick leave it will represent how many sick leave have been taken by each employee and what category.
tblSocialinsurance
This table for holding information of social insurance and payments for each employee and deductibles if any.
tblSponsors
Its work like this I don’t know I have made it correctly in relationships or not, each employee has one ResidentID and each residentID has one WorkPermit in both (ResidentID & WorkPermit) sponsorship is common who sponsor the employee for this two identities.
tblSponsorship
This is the term when someone get hired, we need to change his sponsorship and transfer him to our company so, this table records that process and shows the cost of doing it.
tblTraining
This table shows how many times and how many trainings are given to each employee or employees.
tblTravelAgents
This table records the details for travel agents who provide the air ticket services, must be connected to airticket table.
tblUserSecurity
This table holds user’s security details such as passwords and questions etc, who use to login in this database.
tblVacations
This table shows how many vacation and leave have been taken by employee/employees and all the related records.
tblVehicles
This table shows the list of vehicle company holds and who is using it, insurance expiry and premium also mentioned each vehicle have multiple insurance and different users for different time frame.
tblVisas
This table records all visa entries for employees for different categories like (Business, vacation ) and with type of entry (Single entry or multiple) etc.
I hope you won't mind helping for above, your most valuable comments and suggestion is always welcomed and appreciated.
thanks & regards,
MA
As promised i am back again from the beginning of my project, attached is tables and relationships that i have made to start this project, please i required your kind help at my database start up version for verifying tables design and its relationship.
All is done already by me now need your views/inputs and suggestion if its required to change any fields or details in attached database.
The purpose of building this database to monitor/report with overall employees records and related stuff.
This database will track and calculate employees usage, benefits & records and in same time it will generate many reports but this will come in my project's next step for now i need to focus on tables and their relationships.
tblAccessLevel
This to give users with access level once they login different users will have different login writes (This table work will come later) need to see the relationship and fields are mentioned correctly.
tblAirtickets
This table is recording the air tickets being issued for employees for various purposes
tblBankDetails
This table is record set for employees bank details where all employees bank account details will be stored.
tblBonus
This is to record the bonuses paid to employees with amount and date of payment
tblCalendar
This is to monitor the weekends and working days as well as holidays for previous years and current changes this is as per the gulf region so i have to tailor it as per hijiri calendar also that not the issue now just for your information.
tblContacts
This is employees contact details Local & Home Country.
TblDepartments
This is departments within the operation area and each employee is belongs to any of this department, I have not completed the allocation but just need to see all relationship and field names are ok
tblDependance
This table holds the details for employee’s family cost and categories that company paying for behalf of employee based on their employment contracts just as Medical & other Government fees for residentID etc.
tblEmployeeEvaluation
This table remains for employees evaluation before paying for (Bonus & Increments) so I have linked this to both tables.
tblEmployees
This table holds employee personal details and employee list with their EmpID as crucial in this database.
tblEmploymentContracts
This table have information about employees contractual agreements and writes and its linked to tblEmployees with PK many calculations will be done based on their writes and data mentioned in this table.
tblEndofService
This table hold the information and records for employees gratuity payments if either its paid by partial or full in one or many payment for single employee.
tblIncreaments
This table hold the information for increments being given to each employee with amount and date of payment.
tblEvaluationPoints
This table to link it with tblEmployeeEvaluation to show the performance points for each category of performance selected for employee and based on this points will be add/subtracted from employees total points.
tblInsuranceProviders
This piece of information show how many insurance provider we have for different category of insurance and could be added more and amended existing.
tblJobTitles
This is list of job titles that represent the job nature and responsibility.
tblWorkPermits
This is kind of work permit details that is linked directly to resident ID so each resident have one work permit and its details are mentioned in this table with fees and renewal terms etc.
tblLifeInsurance
This is employee’s life insurance details and insured amount etc.
tblMedical
This is employee’s medical insurance details and insured amount etc.
tblNationality
This table show all nationalities to select for employee and other requirement of nationality identification.
tblOvertime
This table record the overtime for employee with reason and approval etc
tblPassport
This table to hold the details for employee’s passport validations, this should be linked to ResidentID and employee because employee holds the passport and ResidentID both.
tblPayroll
This table is record of payroll for employees each employee each month have one line with his pay details and also its design for different payroll cycles as per the future requirement.
tblQualifications
This table record the qualification of employees with highest study in employees qualification list one record per employee only.
tblQuestions
This table hold the security questions for Users who use to login in this database this will come in next step just need to see the relationship and field are correct.
tblResidentID
This table have information for employees resident ID and its validations
tblSickleave
This table have sick leave taken and approved, paid for different categories such as maternity and general sick leave it will represent how many sick leave have been taken by each employee and what category.
tblSocialinsurance
This table for holding information of social insurance and payments for each employee and deductibles if any.
tblSponsors
Its work like this I don’t know I have made it correctly in relationships or not, each employee has one ResidentID and each residentID has one WorkPermit in both (ResidentID & WorkPermit) sponsorship is common who sponsor the employee for this two identities.
tblSponsorship
This is the term when someone get hired, we need to change his sponsorship and transfer him to our company so, this table records that process and shows the cost of doing it.
tblTraining
This table shows how many times and how many trainings are given to each employee or employees.
tblTravelAgents
This table records the details for travel agents who provide the air ticket services, must be connected to airticket table.
tblUserSecurity
This table holds user’s security details such as passwords and questions etc, who use to login in this database.
tblVacations
This table shows how many vacation and leave have been taken by employee/employees and all the related records.
tblVehicles
This table shows the list of vehicle company holds and who is using it, insurance expiry and premium also mentioned each vehicle have multiple insurance and different users for different time frame.
tblVisas
This table records all visa entries for employees for different categories like (Business, vacation ) and with type of entry (Single entry or multiple) etc.
I hope you won't mind helping for above, your most valuable comments and suggestion is always welcomed and appreciated.
thanks & regards,
MA