如何设计SQL Server数据库架构实现部门KPI数据月度快照？

Hey there! Let's put together a solid SQL Server database schema that fits your KPI tracking and monthly snapshot needs perfectly. This setup will handle department-specific KPIs, target/actual value management, and read-only historical access smoothly:

We'll start with foundational tables to model your departments, KPI definitions, and monthly data:

Departments Table #

Stores basic info about your teams:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY IDENTITY(1,1),
    DepartmentName VARCHAR(100) NOT NULL UNIQUE,
    Description VARCHAR(255) NULL,
    CreatedDate DATETIME DEFAULT GETDATE(),
    UpdatedDate DATETIME DEFAULT GETDATE()
);

KPITemplates Table #

Defines unique KPIs per department (so Sales can track revenue, HR can track headcount, etc.):

CREATE TABLE KPITemplates (
    KPIID INT PRIMARY KEY IDENTITY(1,1),
    DepartmentID INT NOT NULL FOREIGN KEY REFERENCES Departments(DepartmentID),
    KPIName VARCHAR(100) NOT NULL,
    KPIDescription VARCHAR(255) NULL,
    UnitOfMeasure VARCHAR(50) NULL, -- e.g., "Revenue ($)", "Completion Rate (%)"
    CreatedDate DATETIME DEFAULT GETDATE(),
    UpdatedDate DATETIME DEFAULT GETDATE(),
    UNIQUE(DepartmentID, KPIName) -- No duplicate KPIs per department
);

KPIMonthlyData Table #

Stores target/actual values for each KPI per month, with edit controls:

CREATE TABLE KPIMonthlyData (
    KPIMonthlyID INT PRIMARY KEY IDENTITY(1,1),
    KPIID INT NOT NULL FOREIGN KEY REFERENCES KPITemplates(KPIID),
    ReportingYear INT NOT NULL,
    ReportingMonth INT NOT NULL CHECK (ReportingMonth BETWEEN 1 AND 12),
    TargetValue DECIMAL(18,4) NOT NULL, -- Set by you in the app
    ActualValue DECIMAL(18,4) NULL, -- Entered by end users
    IsLocked BIT DEFAULT 0, -- 0 = editable, 1 = read-only after snapshot
    CreatedDate DATETIME DEFAULT GETDATE(),
    UpdatedDate DATETIME DEFAULT GETDATE(),
    UNIQUE(KPIID, ReportingYear, ReportingMonth) -- One record per KPI per month
);

MonthlySnapshots Table #

Stores read-only historical copies of monthly KPI data (preserves state as of month-end):

CREATE TABLE MonthlySnapshots (
    SnapshotID INT PRIMARY KEY IDENTITY(1,1),
    KPIMonthlyID INT NOT NULL FOREIGN KEY REFERENCES KPIMonthlyData(KPIMonthlyID),
    SnapshotDate DATETIME DEFAULT GETDATE(),
    TargetValue DECIMAL(18,4) NOT NULL,
    ActualValue DECIMAL(18,4) NULL,
    ReportingYear INT NOT NULL,
    ReportingMonth INT NOT NULL
);

Add these indexes to speed up historical queries and data entry:

-- Fast filtering for current KPI data by department and month
CREATE NONCLUSTERED INDEX IX_KPIMonthlyData_Department_Month
ON KPIMonthlyData(KPIID, ReportingYear, ReportingMonth)
INCLUDE(TargetValue, ActualValue, IsLocked);

-- Quick access to historical snapshots by year/month
CREATE NONCLUSTERED INDEX IX_MonthlySnapshots_YearMonth
ON MonthlySnapshots(ReportingYear, ReportingMonth, KPIMonthlyID);

Use this stored procedure to lock monthly data and generate snapshots—you can schedule it with SQL Server Agent to run automatically on the last day of each month:

CREATE PROCEDURE GenerateMonthlySnapshot
    @Year INT,
    @Month INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Lock the month's data to prevent post-snapshot edits
    UPDATE KPIMonthlyData
    SET IsLocked = 1
    WHERE ReportingYear = @Year AND ReportingMonth = @Month;

    -- Copy locked data to the read-only snapshot table
    INSERT INTO MonthlySnapshots (KPIMonthlyID, TargetValue, ActualValue, ReportingYear, ReportingMonth)
    SELECT 
        KPIMonthlyID,
        TargetValue,
        ActualValue,
        ReportingYear,
        ReportingMonth
    FROM KPIMonthlyData
    WHERE ReportingYear = @Year AND ReportingMonth = @Month;
END;

• View editable current-month KPI data for a department:

SELECT 
    d.DepartmentName,
    kt.KPIName,
    kmd.ReportingYear,
    kmd.ReportingMonth,
    kmd.TargetValue,
    kmd.ActualValue
FROM KPIMonthlyData kmd
JOIN KPITemplates kt ON kmd.KPIID = kt.KPIID
JOIN Departments d ON kt.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'Sales' 
    AND kmd.ReportingYear = 2024 
    AND kmd.ReportingMonth = 5
    AND kmd.IsLocked = 0;

• View read-only historical snapshot data:

SELECT 
    d.DepartmentName,
    kt.KPIName,
    ms.ReportingYear,
    ms.ReportingMonth,
    ms.TargetValue,
    ms.ActualValue,
    ms.SnapshotDate
FROM MonthlySnapshots ms
JOIN KPIMonthlyData kmd ON ms.KPIMonthlyID = kmd.KPIID
JOIN KPITemplates kt ON kmd.KPIID = kt.KPIID
JOIN Departments d ON kt.DepartmentID = d.DepartmentID
WHERE ms.ReportingYear = 2024 
    AND ms.ReportingMonth = 4;

• Permissions: Set up SQL Server roles to restrict access:
  • Admins: Can modify KPITemplates and set TargetValue
  • Users: Can only update ActualValue in unlocked records
  • Read-only users: Can only access MonthlySnapshots and locked KPIMonthlyData
• Validation: Add triggers to block edits to ActualValue when IsLocked = 1
• Audit Logging (optional): Add an audit table to track changes to target/actual values for compliance

内容的提问来源于stack exchange，提问作者Louitabitbol