3

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])
);
  • 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 Feb 28 '17 at 3:11
  • Hi, the design should be OLTP system – K09 Mar 7 '17 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 Mar 12 '17 at 20:53
  • Again - what is the purpose of you storing period? What does it represent? – blobbles Mar 12 '17 at 22:59
  • The period represents a single date. i.e. the actual date that the Task is recorded for. – K09 Mar 13 '17 at 10:16
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 Mar 7 '17 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 Mar 12 '17 at 23:17
  • Yes, apologies. Period should really be 'TaskRecordDate' i.e. the date that the task is run. – K09 Mar 13 '17 at 10:17

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.