Sql-server – Normalized Table Design 3NF

normalizationsql server

I am building a table that will store 'task records' i.e. who approved a task for a group and period.

I have normalized the table but I am not sure if I have gone beyond 3NF, and if this is overkill?

I have created 6 related tables each represented by a FK in the TaskRecord table. The Model.Period table just contains a date column…

CREATE TABLE [Model].[Period]
(
   [PeriodID] [int] NOT NULL IDENTITY(1,1),
   [Period] [date] NOT NULL
   CONSTRAINT [PK_Period_PeriodID] PRIMARY KEY CLUSTERED([PeriodID] ASC)
);

Is this really necessary or should I store the date value in the TaskRecord table?

Here is the TaskRecord tbl…

CREATE TABLE [Model].[TaskRecord]
(
    [TaskRecordID] [int] NOT NULL IDENTITY(1,1),
    [GroupID] [int] NOT NULL,
    [PeriodID] [int] NOT NULL,
    [TaskID] [int] NOT NULL,
    [StatusFlagID] [int] NOT NULL,
    [ApprovalLevelID] [int] NULL,
    [ApprovedByID] [int] NULL,
    [ApprovedDateTime] [datetimeoffset](0) NULL,
    CONSTRAINT [PK_TaskRecord_TaskRecordID] PRIMARY KEY CLUSTERED  ([TaskRecordID] ASC),
    CONSTRAINT [FK_TaskRecord_GroupID] FOREIGN KEY(GroupID) REFERENCES [Model].[Group]([GroupID]),
    CONSTRAINT [FK_TaskRecord_PeriodID] FOREIGN KEY(PeriodID) REFERENCES [Model].[Period]([PeriodID]),
    CONSTRAINT [FK_TaskRecord_TaskID] FOREIGN KEY(TaskID) REFERENCES [Model].[Task]([TaskID]),
    CONSTRAINT [FX_TaskRecord_StatusFlagID] FOREIGN KEY([StatusFlagID]) REFERENCES [Model].[StatusFlag]([StatusFlagID]),
    CONSTRAINT [FK_TaskRecord_ApprovalLevelID] FOREIGN KEY(ApprovalLevelID) REFERENCES [Model].[ApprovalLevel]([ApprovalLevelID]),
    CONSTRAINT [FK_TaskRecord_ApprovedByID] FOREIGN KEY(ApprovedByID) REFERENCES [Model].[UserDetail]([UserDetailID])
);

Best Answer

I think you have gone too far for a transactional system, however if you are designing a data warehouse, your approach is correct.

If you are designing and OLTP system, I would recommend that you replace "PeriodID" in your "TaskRecord" table with "CreateDate" or something similar, so it implies what you are actually storing. You have lost the definition of what you are storing by having a column named "PeriodID" in your TaskRecord table in addition to potentially losing accuracy (as it is a date, rather than a datetime).

If you are designing and OLAP system however your approach is correct - your Model.Period table is a Dimension table and your Model.TaskRecord table is your fact table (though it appears to be lacking measures). I don't believe you are designing an OLAP system however so will not mention more.

In respect to normal forms, I would suggest you have decreased from 3NF rather than gone beyond. This is because you have introduced a dependency on insertions into the Model.Period table in order to maintain the ability to insert into your Model.TaskRecord table. A clear goal of normalisation according to Codd is "To free the collection of relations from undesirable insertion, update and deletion dependencies". Essentially date/time has no dependency if used in a field which stores a date/time value. However you have introduced a dependency on a suitable date record exiting in the Period table by using your original design.

Related Question