From my understanding of 3NF, there should not be any transitive dependency.
However, this is what I found from the
Production.WorkOrder table. Since
StockedQty is dependent on
ScrappedQty, there is a transitive dependency in this table, am I right?
The DDL for the aforementioned table is as follows:
CREATE TABLE [Production].[WorkOrder]( [WorkOrderID] [int] IDENTITY(1,1) NOT NULL, [ProductID] [int] NOT NULL, [OrderQty] [int] NOT NULL, [StockedQty] AS (isnull([OrderQty]-[ScrappedQty],(0))), [ScrappedQty] [smallint] NOT NULL, [StartDate] [datetime] NOT NULL, [EndDate] [datetime] NULL, [DueDate] [datetime] NOT NULL, [ScrapReasonID] [smallint] NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED ( [WorkOrderID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
My questions are:
Is a computed column in fact transgressing 3NF or is it not, considering that such a column cannot be updated manually by a user?
If it is indeed violating 3NF, then why is the computed column shown in the example above?
Ignoring the factor of whether a computed column infringes 3NF or not, what are the criteria of measure to choose between a computed column over computing when querying?