CODEWITHSUNDEEP
mysqldatabase-design
edgarmtze
edgarmtze

Reputation: 25048

Aggregate Query depending on several tables

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.

    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,
      DeptID      INTEGER      NOT NULL,        
      JobID       INTEGER      NOT NULL
    );

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

    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 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_ProjectID  FOREIGN KEY (ProjectID)  REFERENCES Project(ProjectID),
      CONSTRAINT fk_EmployeeID 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', 1, 3);
INSERT INTO `Employee` VALUES (20, 'Moe',  'M', 'Anywhere', '827318322', 2, 3);
INSERT INTO `Employee` VALUES (30, 'Jack', 'M', 'Anywhere', '927418343', 3, 4);
INSERT INTO `Employee` VALUES (40, 'Marge','F', 'Evererre', '127347645', 1, 6);
INSERT INTO `Employee` VALUES (50, 'Greg' ,'M', 'Portland', '134547633', 3, 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);

So 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.

What would be the query to sum all working days knowing the employees involved in a particular project to know the cost generated for their work, Is it possible to know this with this design?

So lets suppose I know that in project 1, 5 employees were involved, and I know by other table "jobs" the salary I would pay each one of them per day

I am doing some queries here with sqlfiddle

UPDATE

 CREATE TABLE `Sexes` (
   Sex char(1) primary key
 );
 INSERT INTO Sexes values ('M');
 INSERT INTO Sexes values ('F');

CREATE TABLE `Employee`(
  EmployeeID  INTEGER      NOT NULL PRIMARY KEY,
  Name        VARCHAR(130)  NOT NULL,
  Sex         CHAR(1)      NOT NULL,
  Address     VARCHAR(380)  NOT NULL,
  Security    VARCHAR(15)  NOT NULL,
  FOREIGN KEY (Sex) references Sexes (Sex),
  CONSTRAINT `uc_EmployeeInfo` UNIQUE (`EmployeeID`,`Name`,`Security`)           
);

CREATE TABLE `Department`  (
  DeptID   INTEGER     NOT NULL PRIMARY KEY,
  DeptName VARCHAR(30) NOT NULL,
  CONSTRAINT `uc_DeptName` UNIQUE (`DeptID`,`DeptName`)
);

CREATE TABLE `Dept-Employee`(
  EmployeeID   INTEGER NOT NULL,          
  DeptID       INTEGER NOT NULL,     
  CONSTRAINT fk_DeptID     FOREIGN KEY (DeptID)     REFERENCES `Department`(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 `Department`(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        DECIMAL(7,3) NOT NULL default '0000.000', 
  JobSalaryperDay  DECIMAL(7,3) NOT NULL default '0000.000', 
  CONSTRAINT `uc_jobs` UNIQUE (`JobID`,`JobName`)
);

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,
  ProjectName   VARCHAR(200) NOT NULL,
  StartDate     DATE    NOT NULL,
  DaysOfWork    INTEGER NOT NULL,
  NoEmployees   INTEGER NOT NULL,
  EstimatedCost DECIMAL(9,3) NOT NULL default '000000.000', 
  RealCost      DECIMAL(9,3) NOT NULL default '000000.000', 
  CONSTRAINT `uc_project` UNIQUE (`ProjectID`,`ProjectName`)           
);

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 `Department` VALUES (1, 'Outsourcing');
    INSERT INTO `Department` VALUES (2, 'Technician');
    INSERT INTO `Department` VALUES (3, 'Administrative');

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

    INSERT INTO `Employee` VALUES (10, 'Joe',  'M', 'Joewhere', '927318344');
    INSERT INTO `Employee` VALUES (20, 'Moe',  'M', 'Moewhere', '827318322');
    INSERT INTO `Employee` VALUES (30, 'Jack', 'M', 'Jaswhere', '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' , 20, 5, 3000.50, 2500.00);
    INSERT INTO `Project` VALUES (2, 'Second one pro', '2008-8-01' , 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);

To the new structure I did this 

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

Upvotes: 1

Views: 450

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

The data at the end of your question doesn't seem to match the data in your INSERT statements.

Have you ever heard of "divide and conquer"? This is a good time to use it. Here's what I'd do.

create view employee_pay as
select e.*, j.jobname, j.jobsalary, j.jobsalaryperday
from employee 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

I'd do that, because I expect those views to be generally useful. (Especially for debugging.) Having created those views, the total for a project is dead simple.

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

projectid  total_salaries
--
1          1509.91

You really don't want to use DOUBLE for money. Use DECIMAL instead.

Use this query to sort out why my sum doesn't match yours.

select p.*, e.name
from project_pay p
inner join employee e on e.employeeid = p.employeeid;

projectid  employeeid  dayswork  jobsalaryperday  total_salary  name
1          10          20        21.67            433.4         Joe
1          20          11        21.67            238.37        Moe
1          30          17        26.7             453.9         Jack
1          40           8        17.35            138.8         Marge
1          50           8        30.68            245.44        Greg

Anti-patterns

Broken identity

Whenever you see a table like this one

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

you should assume its structure is wrong, and dig deeper. (It's presumed guilty until proven innocent.) The anti-pattern you look for

  • integer as an artificial primary key, along with
  • no other unique constraints.

A table like this allows the real data to be duplicated, eliminating the usefulness of an artificial key.

DeptID  DeptName
--
1       Wibble
2       Wibble
...
175     Wibble

A table like this will allow multiple foreign key references, too. That means some of the foreign keys might reference Wibble (DeptID = 1), some might reference Wibble (DeptID = 175), and so on.

To fix that, add a UNIQUE constraint on DeptName.

Missing foreign key references

Whenever you see a table like this one

CREATE TABLE Employee(
  EmployeeID  INTEGER      NOT NULL PRIMARY KEY,
  Name        VARCHAR(30)  NOT NULL,
  ...
  DeptID      INTEGER      NOT NULL,        
  JobID       INTEGER      NOT NULL
);

you should assume its structure is wrong, and dig deeper. (Again, it's presumed guilty until proven innocent.) The anti-pattern you look for

  • ID numbers from other tables, along with
  • no foreign key constraints referencing those tables.

To fix that, add foreign key constraints for DeptID and JobID. On MySQL, make sure you're using the INNODB engine, too. (As of MySQL 5.6, MyISAM still won't enforce foreign key constraints, but it won't give you an error or warning if you write them. They're parsed and ignored.)

If you come to MySQL from another dbms, you'll be surprised to find that MySQL doesn't support inline foreign key reference syntax. That means you can't write this.

DeptID integer not null references Departments (DeptID)

Instead, you have to write a separate foreign key clause in the CREATE TABLE statement. (Or use a separate ALTER TABLE statement to declare the FK reference.)

DeptID integer not null,
foreign key (DeptID) references Departments (DeptID)

Search this page for "inline ref", but read the whole thing.

Missing CHECK() constraints

MySQL doesn't enforce CHECK() constraints, so for columns that beg for a CHECK() constraint, you need a table and a foreign key reference. When you see a structure like this

CREATE TABLE Employee(
  EmployeeID  INTEGER      NOT NULL PRIMARY KEY,
  Name        VARCHAR(30)  NOT NULL,
  Sex         CHAR(1)      NOT NULL,

the column "Sex" begs for a CHECK() constraint.

CREATE TABLE Employee(
  EmployeeID  INTEGER      NOT NULL PRIMARY KEY,
  Name        VARCHAR(30)  NOT NULL,
  Sex         CHAR(1)      NOT NULL CHECK( Sex IN ('M', 'F')),

But MySQL doesn't enforce CHECK() constraints, so you need another table and a foreign key reference.

create table sexes (
  sex char(1) primary key
);
insert into sexes values ('M');
insert into sexes values ('F');

CREATE TABLE Employee(
  EmployeeID  INTEGER      NOT NULL PRIMARY KEY,
  Name        VARCHAR(30)  NOT NULL,
  Sex         CHAR(1)      NOT NULL,
  ...
  foreign key (Sex) references Sexes (Sex)

I'd consider CHECK() constraints for most of these columns. Some can be implemented as tables with foreign key references.

  • Employee.Security
  • Jobs.JobSalary
  • Jobs.JobSalaryperDay
  • Project.DaysOfWork
  • Project.NoEmployees
  • Project.EstimatedCost
  • Project.RealCost
  • Project_Employee.DaysWork

Using floating-point data types for money

Don't do that. Floating-point numbers are useful approximations, but they're still approximations. Use DECIMAL instead.

Upvotes: 2

Related Questions