Better way to design a database
- by cMinor
I have a conceptual problem and I would like to get your ideas on how I'll be able to do what I am aiming.
My goal is to create a database with information of persons who work at a place depending on their profession and skills,and keep control of salary and projects (how much would cost summing all the hours of work)
I have 3 categories which can have subcategories:
Outsourcing
Technician
welder
turner
assistant
Administrative
supervisor
manager
So each person has its information and the projects they are working on, also one person may do several jobs...
I was thinking about having 5 tables (EMPLOYEE, SKILLS, PROYECTS, SALARY, PROFESSION) but I guess there is a better way of doing this.
create table Employee
(
PRIMARY KEY [Person_ID] int(10),
[Name] varchar(30),
[sex] varchar(10),
[address] varchar(10),
[profession] varchar(10),
[Skills_ID] int(10),
[Proyect_ID] int(10),
[Salary_ID] int(10),
[Salary] float
)
create table Skills
(
PRIMARY KEY [Skills_ID] int(10),
FOREIGN KEY [Skills_name] varchar(10) REFERENCES Employee(Person_ID),
[Skills_pay] float(10),
[Comments] varchar(50)
)
create table Proyects
(
PRIMARY KEY [Proyect_ID] int(10),
FOREIGN KEY [Skills_name] varchar(10) REFERENCES Employee(Person_ID)
[Proyect_name] varchar(10),
[working_Hours] float(10),
[Comments] varchar(50)
)
create table Salary
(
PRIMARY KEY [Salary_ID] int(10),
FOREIGN KEY [Skills_name] varchar(10) REFERENCES Employee(Person_ID)
[Proyect_name] varchar(10),
[working_Hours] float(10),
[Comments] varchar(50)
)
So to get the total amount of the cost of a project I would just sum the working hours of each employee envolved and sum some extra costs in an aggregate query.
Is there a way to do this in a more efficient way?
What to add or delete of this small model? I guess I am missing something in the salary - maybe I need another table for that?
