- This is a basic PostgreSQL database schema designed for a Human Resources department to store and manage employee information, insurance options, contracts, payments, and more.
- The schema consists of 16 tables, employing various relational models, domains, functions, etc. to ensure robust data management.
employee: Contains personal and organizational details of employees.department: Manages department information.employee_department: Establishes the relationship between employees and their respective departments.position: Stores position titles with associated schedules.employee_position: Faciliates the assignment of employees to different positions accross various departments.schedule: Manages schedule details for different positions.insurance: Holds information about available insurance plans.employee_insurance: Links employees to their chosen insurance option, including coverage for family members.contract: Tracks contract details, including start and end dates, and an encrypted serial number for each contract.payment: Stores payments records for employees based on their contracts.payment_currency: Holds several payment currencies.employee_pn: Stores personal and office contact numbers , including optional extensions.address: Records address details for employees accross different cities.employee_address: Establishes the relationship between employees and their addresses.city: Lists cities within each country.country: Contains a comprehensive list of countries.
- Employees can be associated with multiple departments simultaneously, holding different positions within each.
- Each employee can have distinct schedules tailored to their various positions.
- An insurance module enabling employees to enroll in one option per year, and optionally including family members in the same or different option.
- A contract system streamlining employee payments.
Run the schema-structure SQL file that contains all essential tables, attributes, and related components.
Special thanks to @MichaelaRif for contributing