Database Design Table for forecasting

database-design

I am designing a database to mainly share data regarding projects and their progress. Part of this is to be able to store data regarding the forecast of each table.
I am designing the ForecastTbl and I am not sure how to proceed.
The forecast will hold the step during the project and how much is spent for 5 years. How do i design the table. i was thinking of having a ForecastTbl, YearTbl, MilestoneTbl…
For example:

Milestone Name     2016/2017     2017/2018  2018/2019   2019/2020   2020/2021

Construction         ...$           ...$       ...$         ...$      ...$

Security             ...$           ...$       ...$         ...$      ...$

Travel               ...$           ...$       ...$         ...$      ...$

Best Answer

The "typical" method for accomplishing this type of design is something like this:

enter image description here

You could use pivot functionality to show the data in a table format, similar to your example. In SQL Server this might look something like:

SELECT *
FROM (
    SELECT names.MilestoneName
        , FY = CONVERT(varchar(4), YEAR(dates.MilestoneStartDate)) 
                + '-' + CONVERT(varchar(4), YEAR(dates.MilestoneEndDate))
        , [Money Spent(Budgeted)] = CONVERT(varchar(20), details.MoneySpent) 
                + ' (' + CONVERT(varchar(20), details.MoneyBudgeted) + ')'
    FROM dbo.MilestoneDetails details
        INNER JOIN dbo.MilestoneDates dates 
            ON details.MilestoneDateID = dates.MilestoneDateID
        INNER JOIN dbo.MilestoneNames names 
            ON details.MilestoneID = names.MilestoneID
) src
PIVOT (
    MAX(src.[Money Spent(Budgeted)])
    FOR
        src.FY IN (
            [2015-2016]
            , [2016-2017]
            , [2017-2018]
        )
    ) pvt

╔═══════════════╦══════════════════════╦══════════════════════╗
║ MilestoneName ║ 2015-2016            ║ 2016-2017            ║
╠═══════════════╬══════════════════════╬══════════════════════╣
║ Construction  ║ 400.0000 (900.0000)  ║ 700.0000 (1200.0000) ║
║ Security      ║ 500.0000 (1000.0000) ║ 800.0000 (1300.0000) ║
║ Travel        ║ 600.0000 (1100.0000) ║ 900.0000 (1400.0000) ║
╚═══════════════╩══════════════════════╩══════════════════════╝

This is the code to create the tables, etc, if anyone is interested:

IF OBJECT_ID(N'dbo.MilestoneDetails', N'U') IS NOT NULL
DROP TABLE dbo.MilestoneDetails;
IF OBJECT_ID(N'dbo.MilestoneDates', N'U') IS NOT NULL
DROP TABLE dbo.MilestoneDates;
IF OBJECT_ID(N'dbo.MilestoneNames', N'U') IS NOT NULL
DROP TABLE dbo.MilestoneNames;

CREATE TABLE dbo.MilestoneDates
(
    MilestoneDateID int NOT NULL IDENTITY(1,1) 
        CONSTRAINT PK_MilestoneDates
        PRIMARY KEY CLUSTERED
    , MilestoneStartDate datetime NOT NULL
    , MilestoneEndDate datetime NOT NULL
);

CREATE TABLE dbo.MilestoneNames
(
    MilestoneID int NOT NULL IDENTITY(1,1) 
        CONSTRAINT PK_MilestoneNames
        PRIMARY KEY CLUSTERED
    , MilestoneName varchar(48) NOT NULL
);

CREATE TABLE dbo.MilestoneDetails(
    MilestoneID int NOT NULL
    , MilestoneDateID int NOT NULL
    , MoneySpent decimal(18, 4) NOT NULL
    , MoneyBudgeted decimal(18, 4) NOT NULL
    , CONSTRAINT PK_MilestoneDetails 
        PRIMARY KEY CLUSTERED 
        (MilestoneID ASC, MilestoneDateID ASC)
);

ALTER TABLE dbo.MilestoneDetails  WITH CHECK 
ADD  CONSTRAINT FK_MilestoneDetails_MilestoneDates FOREIGN KEY(MilestoneDateID)
REFERENCES dbo.MilestoneDates (MilestoneDateID)

ALTER TABLE dbo.MilestoneDetails CHECK CONSTRAINT FK_MilestoneDetails_MilestoneDates

ALTER TABLE dbo.MilestoneDetails  WITH CHECK 
ADD  CONSTRAINT FK_MilestoneDetails_MilestoneNames FOREIGN KEY(MilestoneID)
REFERENCES dbo.MilestoneNames (MilestoneID)

ALTER TABLE dbo.MilestoneDetails CHECK CONSTRAINT FK_MilestoneDetails_MilestoneNames

INSERT INTO dbo.MilestoneNames (MilestoneName)
VALUES ('Construction')
    , ('Security')
    , ('Travel');

INSERT INTO dbo.MilestoneDates (MilestoneStartDate, MilestoneEndDate)
VALUES (N'2015-01-01T00:00:00', N'2016-01-01T00:00:00')
    , (N'2016-01-01T00:00:00', N'2017-01-01T00:00:00')

INSERT INTO dbo.MilestoneDetails (MilestoneID, MilestoneDateID, MoneyBudgeted, MoneySpent)
VALUES (1, 1, 900, 400)
    , (2, 1, 1000, 500)
    , (3, 1, 1100, 600)
    , (1, 2, 1200, 700)
    , (2, 2, 1300, 800)
    , (3, 2, 1400, 900);
Related Question