如何将数据库规范化至3NF？关于joistcost等字段的处理困惑求助

Got it, let's break this down step by step because normalizing to 3NF when you have calculated fields can feel tricky at first! The core principle here is that 3NF strongly discourages storing derived/calculated values—these should be computed on the fly instead of saved directly, since stored calculated data creates redundancy and update anomalies (like if you change joistcost but forget to update floorcost, your data becomes inconsistent immediately).

Let’s start with your specific fields and map them to a 3NF-compliant structure:

• joistcost: Single component cost (this is a raw, non-derived value—we keep this, but in the right table)
• floorcost: joistcost * width (derived value—do NOT store this)
• projecttotal: Sum of all floorcost for a project (also derived—do NOT store this)

Step 1: Split data into logical, single-purpose tables #

We need to eliminate redundancy and ensure each table only holds data tied to one entity:

1. joists table (stores component master data)

   • Columns: joist_id (primary key), joist_type (e.g., "steel I-joist", "wood joist"), joist_cost (your joistcost field—single unit cost)
   • Why? This way, each component’s cost is stored exactly once, not repeated for every floor that uses it. No redundant copies mean no sync issues later.

2. projects table (stores project-level details)

   • Columns: project_id (primary key), project_name, client_name, start_date, etc. (any attributes specific to the entire project)

3. floors table (links projects to components, stores floor-specific data)

   • Columns: floor_id (primary key), project_id (foreign key linking to projects.project_id), joist_id (foreign key linking to joists.joist_id), floor_width (the width value used to calculate floorcost)
   • Why? Each floor belongs to one project, uses one joist type, and has its own width. We don’t store floorcost here because we can calculate it by joining with the joists table.

Step 2: Calculate your "missing" fields when you need them #

Instead of storing floorcost and projecttotal, compute them with queries whenever you need the data:

• To get a single floor’s floorcost:

  SELECT 
    f.floor_id,
    j.joist_cost * f.floor_width AS floorcost
  FROM floors f
  JOIN joists j ON f.joist_id = j.joist_id
  WHERE f.floor_id = [your_floor_id];
  

• To get a project’s total projecttotal:

  SELECT 
    p.project_id,
    p.project_name,
    SUM(j.joist_cost * f.floor_width) AS projecttotal
  FROM projects p
  JOIN floors f ON p.project_id = f.project_id
  JOIN joists j ON f.joist_id = j.joist_id
  WHERE p.project_id = [your_project_id]
  GROUP BY p.project_id, p.project_name;
  

Step 3: Confirm this fits 3NF rules #

Let’s verify against the three normal forms:

1. 1NF: All columns have atomic values (yes—each field holds one discrete piece of data, no repeating groups)
2. 2NF: No partial dependencies (all non-primary key columns depend entirely on the table’s primary key. For example, in floors, project_id, joist_id, and floor_width all rely on floor_id; in joists, joist_cost depends solely on joist_id)
3. 3NF: No transitive dependencies (no non-primary key column depends on another non-primary key column. Since joist_cost lives in the joists table, there’s no indirect dependency between floor_width and joist_cost in the floors table)

This setup keeps your data consistent, cuts down on redundant storage, and avoids the headache of updating calculated values every time a raw value changes. If you ever need to adjust a joist’s cost, you only update it once in the joists table—all future calculations will automatically use the new value.

内容的提问来源于stack exchange，提问作者rbittle2