SQL表规范化：单/双表方案及tbl_code表转换实现求助

Got it, let's break this down step by step. First, we'll convert your existing wide table into the single normalized detail table you want, then we'll cover splitting it into two tables for a more robust normalized design.

This process is called unpivoting—turning column-based code values into row-based entries. The most universal approach (works across all major databases like MySQL, SQL Server, PostgreSQL) uses UNION ALL to stack the rows from each code column:

SELECT Ucode AS `u code`, `desc`, 'code1' AS code, code1 AS value
FROM tbl_code
UNION ALL
SELECT Ucode AS `u code`, `desc`, 'code2' AS code, code2 AS value
FROM tbl_code
UNION ALL
SELECT Ucode AS `u code`, `desc`, 'code3' AS code, code3 AS value
FROM tbl_code
ORDER BY `u code`, code;

How this works: #

• Each SELECT block targets one of your code columns (code1, code2, code3).
• We hardcode the code value (like 'code1') to label which original column the value came from.
• UNION ALL combines all these rows into one result set (use UNION instead if you want to remove duplicate rows, but UNION ALL is faster since it skips deduplication).
• The ORDER BY clause organizes the results to match your example, grouping all entries for a single Ucode together and sorting by code type.

Note: desc is a reserved keyword in most databases, so we wrap it in backticks (`) to avoid syntax errors. If you're using PostgreSQL, use double quotes ("desc") instead.

For better data integrity and to avoid repeating the desc value across every row, we can split this into two tables following second normal form (2NF)—eliminating partial dependencies where non-key columns depend on only part of the primary key.

Step 1: Create a Main Table (Unique Ucode + Description) #

This table stores the core, non-repeating information for each Ucode:

-- Create the main table with Ucode as primary key
CREATE TABLE tbl_code_main (
    Ucode INT PRIMARY KEY,
    `desc` VARCHAR(50) NOT NULL
);

-- Insert unique Ucode + desc pairs from the original table
INSERT INTO tbl_code_main (Ucode, `desc`)
SELECT DISTINCT Ucode, `desc`
FROM tbl_code;

Step 2: Create a Detail Table (Code Values) #

This table stores all the code-type and value pairs, linked back to the main table via Ucode:

-- Create the detail table with a foreign key to the main table
CREATE TABLE tbl_code_details (
    id INT AUTO_INCREMENT PRIMARY KEY, -- Optional auto-increment ID for unique row identification
    Ucode INT NOT NULL,
    code VARCHAR(10) NOT NULL,
    value INT NOT NULL,
    FOREIGN KEY (Ucode) REFERENCES tbl_code_main(Ucode)
);

-- Insert the unpivoted code data into the detail table
INSERT INTO tbl_code_details (Ucode, code, value)
SELECT Ucode, 'code1' AS code, code1 AS value
FROM tbl_code
UNION ALL
SELECT Ucode, 'code2' AS code, code2 AS value
FROM tbl_code
UNION ALL
SELECT Ucode, 'code3' AS code, code3 AS value
FROM tbl_code;

Why this is better: #

• If you ever need to update the description for a Ucode, you only have to change it once in tbl_code_main instead of every row in a single table.
• The foreign key ensures you can't have a code entry in the detail table without a corresponding valid Ucode in the main table, preventing orphaned data.

If you're using a database that supports unpivot functions, you can write more concise code:

• SQL Server: Use the built-in UNPIVOT operator:

  SELECT Ucode AS `u code`, `desc`, code, value
  FROM tbl_code
  UNPIVOT (
      value FOR code IN (code1, code2, code3)
  ) AS unpivoted_data;
  

• PostgreSQL: Use UNNEST with arrays to unpivot:

  SELECT 
      Ucode AS "u code",
      "desc",
      unnest(array['code1', 'code2', 'code3']) AS code,
      unnest(array[code1, code2, code3]) AS value
  FROM tbl_code;
  

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