Does this design satisfy 3NF, and can any improvements be made

database-designnormalization

Does this design satisfy 3NF, and can any improvements be made?

If it does not satisfy 3NF – why?

By improvements, I only care if it will seriously affect scalability, efficiency, etc – I'm not interested in personal preferences.

To add more detail, there can be many bids to a project and any employee can create a new bid. Unit price can be different for each project/bid.

enter image description here

Best Answer

The normal forms apply to individual tables. Dependencies are semantic; they depend on what the data means. Normalizing by relying on column names alone is not very reliable.

  • TblClient is in 5NF.
  • TblEmployee might have a transitive dependency and be only in 2NF, if EmployeeNm->EmployeeRole.
  • TblItem might have a transitive dependency and be only in 2NF, if (ItemType, ItemDesc)->Size, or if ItemDesc->Size.
  • TblProject might have a transitive dependency and be only in 2NF, if (ProjectSite, EstBeginDt)->EstCompleteDt, or if (ProjectSite, EstBeginDt)->anything else.

Every one of these tables almost certainly needs an additional unique constraint on one or more columns. Otherwise you're likely to end up with data that looks like this.

ItemID  ItemType  ItemDesc                Size
--
245     Flange    Engine mounting flange  13mm
246     Flange    Engine mounting flange  13mm
247     Flange    Engine mounting flange  13mm
451     Flange    Engine mounting flange  13mm
457     Flange    Engine mounting flange  13mm
683     Flange    Engine mounting flange  13mm

That particular table needs an additional unique constraint on either {ItemType, ItemDesc} or on {ItemType, ItemDesc, Size}.

Reaching 3NF for every table doesn't necessarily mean you have a good design. You might well have missed critical requirements. If you missed a critical requirement, you're not likely to discover it by normalizing to 3NF or 5NF.

Whoever taught you to name all tables "Tbl"-something or "Lk"-something or "Xref"-something did you no favors. It's likely that person didn't understand logical data independence, and it's likely you don't understand it either. That's a big deal, because it's one of the main features of the relational model, and SQL supports it fairly well. Name tables for what they are, not for how you intend to use them today.