Mysql – DB Schema for Timesheet

MySQLPHP

Hi I want to create a web based resource tracking system which will be used by my manager using php and html. The db i am using is MySql. I have already created tables for employee, project etc. I have to create table to store the weekly resource availability. For eg. an employee A is working in 2 projects this week, and the ratio is like 0.4 and 0.6 (combining both we get 1.00 so he is engaged in project this week completely). Next week he is working on 1 project and his availability for that project is 0.7(so the manager can put him on another project also). I have to store these information for next 20 weeks. I am not getting any logic to implement this idea in DB. The below screenshot is how my db looks like now. The dates are hard coded. enter image description here

Also next week i need to delete the previous weeks information and add 21st week's information for an employee. I completely stuck with this. I saw this link database schema for timesheet but it is some what complex which deals with hours also. I just need the information on weekly basis. Can somebody help me to do this schema ?

Best Answer

I think this could help you.

I create this structure:

Employee
- EmpID (PK)
- EmpName
- TKID

Domain
- DomID (PK)
- DomName

Project
- ProjectID (PK)
- ProjectName
- ProjectStart
- ProjectEnd
- DomID (Asuming your projects are for Domains)

ProjectType
- ProTypeID (PK)
- ProTypeName

RTS
- ProjectID (PK)
- EmpID (PK)
- ProTypeID (PK)
- Active (PK)
- DateStart (PK)
- DateEnd

I fill the tables with the information you post on the screenshot (Only the RTS information is from 2015-06-01 to 2015-06-09).

Employees:

enter image description here

Domains:

enter image description here

Projects:

enter image description here

ProjectTypes:

enter image description here

RTS:

enter image description here

Query:

        SELECT
        E.EmpID,
        E.EmpName,
        E.TKID,
        D.DomName,
        P.ProjectName,
        PT.ProTypeName,
        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-01' AND '2015-06-05' && RTS.Active IS TRUE THEN RTS.DateStart END)/5),0.0),1) AS '2015_06_[01-05]',
        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-08' AND '2015-06-13' && RTS.Active IS TRUE THEN RTS.DateStart END)/5),0.0),1) AS '2015_06_[08-12]'
    FROM Employe as E
    LEFT JOIN RTS on (RTS.EmpID = E.EmpID)
    LEFT JOIN Project as P ON (P.ProjectID = RTS.ProjectID)
    LEFT JOIN Domain as D ON (D.DomID = P.DomID)
    LEFT JOIN ProjectType as PT ON (PT.ProTypeID = RTS.ProTypeID)
    GROUP BY E.EmpID;

Result:

enter image description here

The only problem with this is that you have to put the week date parameters you want to calculate. You can make a Stored Procedure that could read eStartDate and eEndDate variables and calculate the weeks between those dates, and all you have to do is make a Dynamic Query that concatenate every week between the parameters.

Example:

CALL SP_RTS('2015-06-01','2015-06-31',1);

Between 2015-06-01 and 2015-06-31 exists 4 weeks monday-friday, you'll need to concatenate 4 columns with those dates. I could help you but I dont know if my post if what are you looking for, if it is, let me know.

Related Solutions

Mysql – IP Address lookup star schema design

Assuming you are using SQL Server, you could adapt this to work:

CREATE TABLE [dbo].[IP2Location](
    [ID] [int] NOT NULL CONSTRAINT PK_IP2Location PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
    [ip] [bigint] NULL,
    [country_code] [varchar](2) NULL,
    [country_name] [varchar](255) NULL,
    [region_name] [varchar](255) NULL,
    [city_name] [varchar](255) NULL,
    [latitude] [float] NULL,
    [longitude] [float] NULL,
    [zip_code] [varchar](255) NULL,
    [time_zone] [varchar](255) NULL,
    [isp] [varchar](255) NULL,
    [domain] [varchar](255) NULL,
    [net_speed] [varchar](255) NULL,
    [idd_code] [varchar](255) NULL,
    [area_code] [varchar](255) NULL,
    [IP2LocationTimeStamp] [timestamp] NOT NULL,
);

/*
    Create a non-clustered index on the numeric IP address to speed lookups
    I indexed the country_code field here as well since our lookups use it.
    I INCLUDED country_name, region_name, and time_zone since that is what we 
    typically request in a SELECT statement from this table.
*/
CREATE NONCLUSTERED INDEX [IX_IP2Location_IP2LocationServiceQuery] ON [dbo].[IP2Location]
(
    [ip] ASC,
    [country_code] ASC
)
INCLUDE (   [country_name],
    [region_name],
    [time_zone])
GO

/* 
    Create BIGINT versions of the dotted-ip field ip_address
*/
UPDATE IP2Location SET IP = 
            (CAST(substring(ip_address,1,charindex('.',ip_address)-1) AS BIGINT) * 0x01000000) 
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address))+1, (charindex('.', ip_address, charindex('.', ip_address, charindex('.',ip_address))+1)-charindex('.',ip_address,charindex('.',ip_address)))-1) AS BIGINT) * 0x00010000) 
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1, charindex('.', ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1) - (charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1)) as BIGINT) * 0x00000100) 
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address)+1))+1)+1, len(ip_address) - (charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address)+1))+1))) as BIGINT)
            );

/*
    Function to return a TVF containing location info about the given numeric IP address
    For example:
        SELECT country_code, country_name, region_name, city_name, latitude, longitude, time_zone 
        FROM GetLocationFromIPv4(16777215);
*/
CREATE FUNCTION [dbo].[GetLocationFromIPv4]
(
    @IP bigint
)
RETURNS @IPLoc TABLE
(
    country_code varchar(2)
    , country_name varchar(255)
    , region_name varchar(255)
    , city_name varchar(255)
    , latitude float
    , longitude float
    , time_zone varchar(255)
    , isp varchar(255)
    , domain varchar(255)
    , net_speed varchar(255)
    , idd_code varchar(255)
    , area_code varchar(255)
)
AS
BEGIN
    /*
        TVF to return (typically) a single record from IP2Location as quickly as possible
    */
    INSERT INTO @IPLoc
    SELECT country_code
        , country_name
        , region_name
        , city_name
        , latitude
        , longitude
        , time_zone
        , isp
        , domain
        , net_speed
        , idd_code
        , area_code 
    FROM IP2Location
    WHERE ip = @IP;
    RETURN
END
GO

/*
    Function to return a TVF containing location info about the given dotted-decimal IP address
    For example:
        SELECT country_code, country_name, region_name, city_name, latitude, longitude, time_zone 
        FROM GetLocationFromIPv4('192.168.0.1');
*/
CREATE FUNCTION [dbo].[GetLocationFromIPv4String]
(
    @IP nvarchar(15)
)
RETURNS @IPLoc TABLE
(
    country_code varchar(2)
    , country_name varchar(255)
    , region_name varchar(255)
    , city_name varchar(255)
    , latitude float
    , longitude float
    , time_zone varchar(255)
    , isp varchar(255)
    , domain varchar(255)
    , net_speed varchar(255)
    , idd_code varchar(255)
    , area_code varchar(255)
)
AS
BEGIN
    /*
        TVF to return (typically) a single record from IP2Location as quickly as possible
    */
    DECLARE @IPInt64 bigint;
    DECLARE @n1 bigint;
    DECLARE @n2 bigint;
    DECLARE @n3 bigint;
    DECLARE @n4 bigint;

    set @n1 = substring(@IP
        ,1
        ,charindex('.',@IP)-1
        );

    set @n2 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP))+1
            , (charindex('.', @IP, charindex('.', @IP, charindex('.',@IP))+1)-charindex('.',@IP,charindex('.',@IP)))-1
        );

    set @n3 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1
            , charindex('.', @IP, charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1) - (charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1)
        );

    set @n4 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP,charindex('.',@IP,charindex('.',@IP)+1))+1)+1
            , len(@IP) - (charindex('.',@IP,charindex('.',@IP,charindex('.',@IP,charindex('.',@IP)+1))+1))
        );

    SET @IPInt64 = (@n1 * 0x01000000) + (@n2 * 0x00010000) + (@n3 * 0x00000100) + @n4;

    INSERT INTO @IPLoc
    SELECT TOP(1)country_code
        , country_name
        , region_name
        , city_name
        , latitude
        , longitude
        , time_zone
        , isp
        , domain
        , net_speed
        , idd_code
        , area_code 
    FROM IP2Location
    WHERE ip = @IPInt64;
    RETURN
END
GO

Mysql – How to get MySQL to iterate a subquery in an aggregate select

(sum(amount * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50)))) as cost Is the answer, as provided by ypercube.

I was victim to order of operations--MySQL was taking the sum of gallons pumped, then multiplying that against the first/last result found from the gas prices sub query. As Michael quoted and noted:

"The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

So to get what I was after, the sum of each entry multiplied against the price of gas during that week, you simply wrap that exact expression in the comfortable embrace of a sum().

Best Answer

Related Solutions

Mysql – IP Address lookup star schema design

Mysql – How to get MySQL to iterate a subquery in an aggregate select

Related Question