2

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               ...$           ...$       ...$         ...$      ...$    
  • 1
    What information do you envision in each of these tables? A standard database design is based on normalization; basically, this boils down to "don't store the same information in two different places." If your schema avoids that, and isn't hard-coded to X years at a time, you're probably on the right track. – Jon of All Trades Aug 21 '17 at 16:13
1

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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