# Is a functional dependency on a "child" table allowed in third normal form (3NF)?

Consider two simple tables, `OrderHeader` and `OrderDetail` which contain, respectively, the data shown below:

``````| OrderHeaderID | CustomerID | OrderTotal |
|---------------|------------|------------|
| 1             | 124        | 14.5       |
| 2             | 525        | 35.6       |
| etc           |            |            |
``````

# OrderDetail

``````| OrderDetailID | OrderHeaderID | ProductID |
|---------------|---------------|-----------|
| 1             | 1             | 415       |
| 2             | 1             | 52        |
| etc           |               |           |
``````

The `OrderTotal` column can be calculated by adding the price of each product from the `OrderDetail` table. That sounds like a functional dependency! ie:

• `SUM(OrderDetail.Product.Price) → OrderHeader.OrderTotal`

...but afaik it's not a violation of 3NF.

Is this actually allowed? If yes, is it disallowed in higher normal forms?

• stackoverflow.com/a/45189447/3404097 Commented Dec 23, 2018 at 2:34
• "SUM(OrderDetail.Product.Price) -> OrderHeader.OrderTotal" doesn't make sense. "FD" has a definition & it's re one table & two sets. (And it does not involve SQL "aggregate function" names, which anyway are not the names of functions of one parameter.) If you want your reasoning corrected then you need to say it clearly instead of misusing notation & terms. Otherwise we can only pointlessly reproduce yor textbook & show that what you wrote doesn't make sense. (There's no limit to things one could notice & worry about. Just memorize & apply definitions, theorems, algorithms & heuristics.) Commented Dec 23, 2018 at 2:46
• @philipxy Thanks, I didn't realize that normalization exclusively focused on single tables. Is there a reason Codd et al were only concerned with table level dependencies? Isn't an inter-table dependency just as likely to cause an insert/update/delete anomaly? Commented Dec 23, 2018 at 7:36
• i think this type of constraint would mean the the database is not in DKNF (Domain Key Normal Form). Commented Dec 24, 2018 at 16:35
• @yper-crazyhat-cubeᵀᴹ Any constraint not implied by column types (domains) & CKs violates DKNF. DKNF is not in the chain of NFs reached by lossless decomposition per FDs & JDs (which ends at 6NF). It is not of any particular help or concern. What is the point of mentioning it? Commented Dec 25, 2018 at 0:32

There is an informal dependency, but it's not a functional dependency. See the wiki article for the formal definition. Here's an excerpt in simple terms:

In simple words, if the values for the X attributes are known (say they are x), then the values for the Y attributes corresponding to x can be determined by looking them up in any tuple of R containing x.

There is no normal form definition which precludes summarizing child record values in the parent record. However, lots of people would suggest against doing this in transactional databases, particularly where there's a possibility that a child record could be updated (or deleted, or more child records added) such that there's a risk of the attribute in the parent becoming inconsistent with the aggregate value of the children.

If your `OrderHeader` table had a `CustomerName` column, then that would violate 3NF because of the transitive functional dependency on `CustomerID`.

Being informal, a lot of people would say that `OrderTotal` is denormalized because it's susceptible to things that look like insert, update and delete anomalies and so people draw a comparison with the formal definitions of normalization and normal forms (since these are also about avoiding insert/update/delete anomalies). There are some sticklers, though, who will be adamant that avoiding summary attributes in parent records is not the same thing as normalization per se.

• Why isn't this a functional dependency on the child table? For any set of `OrderDetail` tuples with the same `ProductID`, I'll necessarily have the same `OrderTotal`. Isn't that the exact definition of a functional dependency? Commented Dec 22, 2018 at 21:52
• @just.another.programmer Not quite. Follow the link to the wiki article for the precise definition. The definition is that for any particular value of the dependent attribute the determining attribute is always known and consistent. By definition a functional dependency that is actionable for purposes of normalization involves predicates in the same relation. Functional dependencies don't, by definition, involve multiple records. They involve columns in the same record. Commented Dec 22, 2018 at 23:38
• That seems like an arbitrary distinction. I thought the point of functional dependencies was to identify and eliminate attributes that are susceptible to insert/update/delete anomalies. Why should we only worry about that in a single table? Commented Dec 23, 2018 at 7:37
• @just.another.programmer Normalization is about knowing when and how to break up a table to avoid possible anomalies. Database normalization is not the only principle or best practice that can be applied to database design. There can be data redundancies, bad choice of keys, circular references, inappropriate physical representations of data types and other categories of "how not to database" which are all useful in their various ways. Functional dependencies come into play in two of the normal forms (one transitive, the other partial) otherwise there are other considerations. Commented Dec 23, 2018 at 20:55