CODEWITHSUNDEEP
database-designdatabase-schemardbmsrelationaldatabase-normalization
Clyde J Tuitt
Clyde J Tuitt

Reputation: 19

Can I Have FK in 2NF and 3NF in Relational Schema?

So basically I'm Normalizing an Invoice, is it wrong to include FK *INV_NUM* in all 2NF RELATIONAL SCHEMA'S. This is what I already have.

the * Shows PK

1NF (*INV_NUM, INV_DATE, C_ID, C_NAME,C_STR,C_STATE,PART_NUM, PART_DESC, PART_QUANUSED, PART_PRICE, LBR_NUM, LBR_DESC, LBR_PRICE,TAX_RATE)

PARTIAL DEPENDENCIES

TRANSITIVE DEPENDENCIES

2NF CUSTOMER (*C_ID, C_NAME,C_NAME,C_STR,C_STATE)

2NF PART (*PART_NUM, PART_DESC, PART_QUANUSED, PART_PRICE)

2NF LABOR (*LBR_NUM, LBR_DESC, LBR_PRICE)

Upvotes: 1

Views: 383

Answers (1)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

So basically I'm normalizing an invoice, ...

Actually no, not really.

Invoices are temporal by nature, so that INV_DATE is extremely important.

In other words,
FD is not {C_STATE} -> {TAX_RATE}, but {C_STATE, INV_DATE} -> {TAX_RATE}.

FD is not {C_ID} -> {C_STATE}, but {C_ID, INV_DATE} -> {C_STATE}.

FD is not {PART_NUM} -> {PART_PRICE}, but {PART_NUM, INV_DATE} -> {PART_PRICE}.

etc, ...

So your options are

  1. Leave (the invoice table) as it is (seems OK)

  2. Make all temporal.

It is common design for invoices (also purchase orders ...) to "capture and freeze" all relevant info at the time.

Upvotes: 1

Related Questions