Startup Tables & relationships for employee's database (1 Viewer)

[mba_110]

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

 

[Ranman256]

I think some of these can be combined. You don't need a table for everything.
Like absences, the EMP would have
EmpID, Date, AbsType
99, 1/1/2017, VACA
99, 2/2/2017, SICK

Set absence codes, not separate tables. There would be a table for absent codes.

 

[plog]

Yeah, you overnormalized and have poor relationships.

Like Ranman said--you have too many tables. He gave just one example of how you did this--I am certain you've done this other ways as well. Wherever you store a value in a table or field name (e.g. tblVacation, tblSick) you are doing it wrong. That value (Vacation, Sick) should be stored in a field not in a table/field name.

Also, relationships should look like a tree--with only one way to travel from any branch to the other. What you have now is more spiderweb--multiple paths among tables. For example, I can trace at least 2 ways to move between tblEmployees and tblSponsors--that is not correct. There should only be one way to get from tblEmployees and tblSponsors. Again, that's just one example, I am certain you have more issues like this.

Lastly, you have a few tables that don't need to exist. tblCalendarTable, besides doubly redundantly named, serves no purpose that I can see. Access has built in functions (https://www.techonthenet.com/access/functions/) that can easily determine the day/day of the week/ quarter/ year, etc. from a date. Use those not this table.

 

[mba_110]

Ok guys

@Ranman256

I think some of these can be combined. You don't need a table for everything

I glad to have your views and always welcome.

Like absences, the EMP would have
EmpID, Date, AbsType
99, 1/1/2017, VACA
99, 2/2/2017, SICK

Set absence codes, not separate tables. There would be a table for absent codes.

ok so your understanding is little bit wrong here, Vacation doesn't mean they are absent let me explain it to you more clearly.

Every employee has a different Employment contract and different eligibility for no's of air tickets (Including dependents), vacation etc and this need to be tracked and see every time they apply for new vacation subtracting their utilization will give the balance. if i go with your theory employees table is just for list of employees with their details, if any new employee came it will be added to this.

1. where i will record the utilization? and
2. where i will record the list of vacation they took so far, based on these tables i need to build a report for all in future once data base is completed?

Can you tell now how i can do it above in a simple way?

 

[plog]

1. We have no idea of your concept of utilization. Where are you storing it now?

2. You will store it in the table Ran laid out. This table will include all their absences, not just vacation.

Not everything has to be stored in tables. Looking through your tables more closely, I see you are using calculated fields a lot--don't. Instead you need to build queries to calculate that information for you. Sort of like how you need to build queries to determine total vacation taken (and probably to calculate utilization).

 

[Ranman256]

I called it absence, but it could be tSubTable.
it tracks all items an employee would do,
flights, sick, vaca,etc
whatever you can assign a code to.

 

[The_Doc_Man]

Let's take a look at another way of seeing the correct relationships.

Tables describe entities. Entities have properties a.k.a. attributes. An entity COULD be a person but could also be a single airline ticket or a sick day taken by a single person. Entities are things you need to track as part of your business model. Their attributes usually describe separate details of the entity, details that occur (with potentially different values) for each entity of the same class.

Let's examine the concept of USERS. You have a table for Access Level and a table for user login information. Both of these things are described in a way that they seem to be attributes of users. It would make sense to have a table for the names and properties of named access levels, in which case each user would have an Access Level field as a foreign key (FK) to the Access Level table. (This presumes user access levels are single-valued.) However, it would NOT make sense to have one Access Level table entry for each user if you also have a user table.

You might have one Access Level table entry for each possible level of access, like "Unclassified" "Sensitive" "Confidential" "Secret" "Top Secret" and "TS/Compartmented." Six levels no matter how many users you have. But each user would have one code referencing this table, with the default value being "Unclassified."

When you over-normalize your tables, one way to realize that you have done so is that you see a need for a one-to-one relationship between two tables. While not ALWAYS a sign of over-normalization, it FREQUENTLY suggests that you have a problem with two tables that separately contain attributes of the same entity. In Access, the true need for a one-to-one relationship is not zero but is VERY rare. I know of only two valid reasons why such things are done and it doesn't appear to me that your description would lead to either reason being invoked. On the other hand, any one-to-many (or many-to-one) case CERTAINLY needs to be maintained in separate tables.

You can do a fast mental check on whether your table should be split based on the cardinality of the relationship. 1-to-1 = review and coalescence suggested. Other than 1-to-1 - split probably imperative.