From my understanding of 3NF, there should not be any transitive dependency.

However, this is what I found from the AdventureWorks2012 database Production.WorkOrder table. Since StockedQty is dependent on OrderQty and 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,
        [WorkOrderID] ASC
    ) ON [PRIMARY]

My questions are:

  1. Is a computed column in fact transgressing 3NF or is it not, considering that such a column cannot be updated manually by a user?

  2. If it is indeed violating 3NF, then why is the computed column shown in the example above?

  3. 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?

2 Answers 2


Computed columns are not a construct of the relational model, so it doesn't make sense to try and evaluate them using relational model terms such as 3NF. Computed columns are a proprietary extension to SQL added by some vendors, and since they aren't logically stored (although you may persist them physically) they are not part of the tuple as they are not an attribute of the relation, but some expression based on it.

BTW - if that's the only modeling flaw you find in AdventureWorks, and most other sample databases for that matter, look closer :-)


  • It doesn't matter to the relational model whether a user or the DBMS puts a value into a relation, nothing is stopping you from dealing with it relationally. It is not the case that "it doesn't make sense to try and evaluate them using relational model terms". However you might not bother to normalize a table with a computed column, treating it as a view of a projection of it that you normalize, or you might not care about the table with the computed column being in a certain low NF, since the DBMS assures that update anomalies due to the presence of that attribute don't violate constraints.
    – philipxy
    Commented May 19, 2020 at 9:31

Vérace: I would say computed columns should be excluded from consideration in the database normalisation process. They are simply a way of facilitating development against the database - they are never actually needed.

You could for example have one called full_name = title + first_name + middle_name + surname + name_suffix for convenience.

This means that when the developer asks for full_name, they never have to worry about what to include - it's all done for them. No checking to see if suffix exists &c. - it's uniform - i.e. always the same, all the time!

Computed columns are fantastic but should not be considered in terms of normalisation!

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.