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

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])
  • Are you designing an OLTP system or an OLAP system? It appears you are designing an OLTP (i.e. a database which has a user front end of some description - used for multiple users to enter data).
    – blobbles
    Commented Feb 28, 2017 at 3:11
  • Hi, the design should be OLTP system
    – K09
    Commented Mar 7, 2017 at 12:23
  • Any reason why I should include period as a datetype rather than a FK? Should I so similar with ApprovedByID or with other FKs?
    – K09
    Commented Mar 12, 2017 at 20:53
  • Again - what is the purpose of you storing period? What does it represent?
    – blobbles
    Commented Mar 12, 2017 at 22:59
  • The period represents a single date. i.e. the actual date that the Task is recorded for.
    – K09
    Commented Mar 13, 2017 at 10:16

1 Answer 1


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.

  • Yeah it should be OLTP. So if I remove the Period table and just add a datetime column called Period to the TaskRecord table then it is a valid OLTP design?
    – K09
    Commented Mar 7, 2017 at 12:39
  • Sure but "Period" doesn't have any meaning. Period of what? Is it the create date? Or the update date? Or the delete date?
    – blobbles
    Commented Mar 12, 2017 at 23:17
  • Yes, apologies. Period should really be 'TaskRecordDate' i.e. the date that the task is run.
    – K09
    Commented Mar 13, 2017 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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