Search code examples
databaseforeign-keysderived

Database Derived Value same as foreign key?

Is database derived value the same as foreign key?

Based on connoly and begg books, fourth edition page 352 derived value is An attribute that represents a value that is derivable from the value of a related attribute or set of attributes, not necessarily in the same entity type.

If we have 2 table such as, CUSTOMER and ORDER, 

Customer
- Id_Cust
- Name
- Phone

ORDER
- Order_id<
- id_cust

Can we say "ORDER.id_cust is derived from Customer.id_cust" ?

Actually, I'm really confused about the concept above.

Solution

  • No, foreign keys and derived values are two different things.

    product  quantity  price  subtotal
--
ACH123   5         $1.50  $7.50

    In the simplified table above, "subtotal" is a derived value. It's derived from "quantity" and "price". (By multiplying them together.)

    Database designers usually don't store derived values without using a CHECK() constraint or a trigger to make sure the derived value is always correct. In the table above, updating the quantity to 4 would make the derived value "subtotal" incorrect. A CHECK() constraint can prevent an update to "quantity" unless the update also has the correct value for "subtotal". A trigger can automatically update "subtotal" when either "quantity" or "price" changes.

    CHECK() constraints are usually a better choice.