Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I am doing a database design I want to create a database with information of employees, their jobs, salaries and projects.

I want to keep information of the cost of a project (real value of project and the days a employee invested).

For Employee and Project, each Employee has one role on the Project through the PK constraint, and allows for the addition of a new role type ("Tertiary" perhaps) in the future. To get the total amount of the cost of a project and have it for future Work quote, I would just sum the working days of each job for each employee in an aggregate query.

To sum all working days knowing the employees involved in a particular project to know the cost generated for their work I did:

select projectid, sum(total_salary) as total_salaries
from project_pay
group by projectid

Here is the sqlfiddle

There must be a way to improve this a lot:

CREATE TABLE Employee(
  EmployeeID  INTEGER      NOT NULL PRIMARY KEY,
  Name        VARCHAR(30)  NOT NULL,
  Sex         CHAR(1)      NOT NULL,
  Address     VARCHAR(80)  NOT NULL,
  Security    VARCHAR(15)  NOT NULL          
);

CREATE TABLE Departments  (
  DeptID   INTEGER     NOT NULL PRIMARY KEY,
  DeptName VARCHAR(30) NOT NULL
);

CREATE TABLE `Dept-Employee`(
  EmployeeID   INTEGER NOT NULL,          
  DeptID       INTEGER NOT NULL,
  CONSTRAINT fk_DeptID     FOREIGN KEY (DeptID)  REFERENCES Departments(DeptID),
  CONSTRAINT fk_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

CREATE TABLE `Dept-Manager`(
  EmployeeID   INTEGER NOT NULL,          
  DeptID       INTEGER NOT NULL,
  CONSTRAINT fk_DeptIDs     FOREIGN KEY (DeptID)     REFERENCES Departments(DeptID),
  CONSTRAINT fk_EmployeeIDs FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

CREATE TABLE Jobs (
  JobID            INTEGER      NOT NULL PRIMARY KEY,
  JobName          VARCHAR(30)  NOT NULL,
  JobSalary        DOUBLE(15,3) NOT NULL default '0.000', 
  JobSalaryperDay  DOUBLE(15,3) NOT NULL default '0.000', 
  DeptID           INTEGER      NOT NULL
);

CREATE TABLE `Jobs-Employee`(
  EmployeeID   INTEGER NOT NULL,
  JobID        INTEGER NOT NULL,
  CONSTRAINT fk_JobIDs      FOREIGN KEY (JobID)      REFERENCES Jobs(JobID),
  CONSTRAINT fk_EmployeeIDss FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

CREATE TABLE Project(
  ProjectID    INTEGER NOT NULL PRIMARY KEY,
  ProjectDesc   VARCHAR(200) NOT NULL,
  StartDate     DATE NOT NULL,
  EndDate       DATE NOT NULL, 
  DaysOfWork    INTEGER NOT NULL,
  NoEmployees   INTEGER NOT NULL,
  EstimatedCost DOUBLE(15,3) NOT NULL default '0.000', 
  RealCost      DOUBLE(15,3) NOT NULL default '0.000' 
);


CREATE TABLE `Project-Employee`(
  ProjectID    INTEGER NOT NULL,
  EmployeeID   INTEGER NOT NULL,
  Note         VARCHAR(200),
  DaysWork     INTEGER NOT NULL,
  CONSTRAINT fk_ProjectIDsss  FOREIGN KEY (ProjectID)  REFERENCES Project(ProjectID),
  CONSTRAINT fk_EmployeeIDsss FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

INSERT INTO `Departments` VALUES (1, 'Outsourcing');
    INSERT INTO `Departments` VALUES (2, 'Technician');
    INSERT INTO `Departments` VALUES (3, 'Administrative');

    INSERT INTO `Jobs` VALUES (1, 'welder'    ,500.550,16.7 ,2);
    INSERT INTO `Jobs` VALUES (2, 'turner'    ,500.100,16.67,2);
    INSERT INTO `Jobs` VALUES (3, 'assistant' ,650.100,21.67,2);
    INSERT INTO `Jobs` VALUES (4, 'supervisor',800.909,26.70,3);
    INSERT INTO `Jobs` VALUES (5, 'manager'   ,920.345,30.68,3);
    INSERT INTO `Jobs` VALUES (6, 'counter'   ,520.324,17.35,1);

    INSERT INTO `Employee` VALUES (10, 'Joe',  'M', 'Anywhere', '927318344');
    INSERT INTO `Employee` VALUES (20, 'Moe',  'M', 'Anywhere', '827318322');
    INSERT INTO `Employee` VALUES (30, 'Jack', 'M', 'Anywhere', '927418343');
    INSERT INTO `Employee` VALUES (40, 'Marge','F', 'Evererre', '127347645');
    INSERT INTO `Employee` VALUES (50, 'Greg' ,'M', 'Portland', '134547633');

    INSERT INTO `Dept-Employee` VALUES  (10,1);
    INSERT INTO `Dept-Employee`  VALUES (20,2);
    INSERT INTO `Dept-Employee`  VALUES (30,3);
    INSERT INTO `Dept-Employee` VALUES  (40,1);
    INSERT INTO `Dept-Employee`  VALUES (50,3);

    INSERT INTO `Jobs-Employee`  VALUES (10,3);
    INSERT INTO `Jobs-Employee`  VALUES (20,3);
    INSERT INTO `Jobs-Employee`  VALUES (30,4);
    INSERT INTO `Jobs-Employee`  VALUES (40,6);
    INSERT INTO `Jobs-Employee`  VALUES (50,5);




    INSERT INTO `Project` VALUES (1, 'The very first', '2008-7-04' , '2008-7-24' , 20, 5, 3000.50, 2500.00);
    INSERT INTO `Project` VALUES (2, 'Second one pro', '2008-8-01' , '2008-8-30' , 30, 5, 6000.40, 6100.40);


    INSERT INTO `Project-Employee` VALUES (1, 10, 'Worked all days'    , 20);
    INSERT INTO `Project-Employee` VALUES (1, 20, 'Worked just in defs', 11);
    INSERT INTO `Project-Employee` VALUES (1, 30, 'Worked just in defs', 17);
    INSERT INTO `Project-Employee` VALUES (1, 40, 'Contability '       , 8);
    INSERT INTO `Project-Employee` VALUES (1, 50, 'Managed the project', 8);




CREATE VIEW `Emp-Job` as
SELECT e.*,j.jobID  
FROM  Employee e,`Jobs-Employee` j
WHERE e.EmployeeID = j.EmployeeID;



CREATE VIEW `employee_pay` as
select e.*, j.jobname, j.jobsalary, j.jobsalaryperday
from `Emp-Job` e
inner join `Jobs` j
        on e.JobID = j.JobID;

create view project_pay as 
select pe.projectid, pe.employeeid, pe.dayswork,
       e.jobsalaryperday, (e.jobsalaryperday * dayswork) as total_salary
from `Project-Employee` pe
inner join `employee_pay` e
        on e.employeeid = pe.employeeid

Updated code

share|improve this question
    
One change I would prefer is to have a primary key named as "id" for every table. So here Employee table will have the primary key - id, not the "EmployeeID". Doing so Dept-Employee table will also include a primary key along with the foreign key EmployeeID –  Kinjal Nov 7 '12 at 8:37
    
The reason I did that naming was to have a little control of which Primary key I was refering to... but sounds ok –  cMinor Nov 7 '12 at 8:50
    
As for your design, you are making the assumption that nobody will change roles on a project. –  Bob Dalgleish 17 hours ago
add comment

2 Answers 2

A little addition to Jens Schauder's answer,

You shouldn't abbreviate a field name, especially if the field name is a key to a table whose name isn't abbreviated.

  • DeptId primary key and foreign keys should be renamed to DepartmentId.

This assures consistency and helps avoid problems if using some ORMs.

share|improve this answer
    
I updated the code, but do you know how to accomplish @Jens Schauder's point 8? sqlfiddle.com/#!2/0f741 –  cMinor Nov 7 '12 at 19:33
add comment
  • Make the ids of the m-n-mapping tables their PK, you probably don't want to have duplicates there.

  • Address and Name are to short for real world data.

  • Avoid duplication of the table name in columns (e.g. jobname) in table jobs

  • I'd apply that to ids as well, but your approach is ok, as long as it is used consistent.

  • Drop the plural s for tables and constraints, they don't add any value

  • Check all names for typos

  • StartDate + EndDate + DaysOfWork seem to be redundant

  • The structure assumes that all employees work on a project for the complete project. This is completely unrealistic. I'd normally expect a time interval in the employee-job table.

  • Why is there a department in a job?

share|improve this answer
    
I have checked your answer, but I would like to correct the point number 8 "Time interval" could you please explain a little more in how to add that in mysql, I updated the database-design –  cMinor Nov 7 '12 at 17:58
    
The updated code is in sqlfiddle.com/#!2/4744e –  cMinor Nov 7 '12 at 18:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.