基于PGadmin4的多相似数据表规范化咨询

Hey there! Since you're a tech newbie, no worries at all—let's break down how to normalize your year-separated tables into a structure that's way easier to query in pgAdmin4.

Step 1: Create a Unified Master Table #

First, we'll combine all your annual tables into a single master table. This eliminates the need to write UNION queries every time you want cross-year data. We'll add a new global unique primary key (since your original id values are only unique per year) and optional constraints to avoid duplicates.

-- Create the unified table with a new global primary key
CREATE TABLE unified_data (
    unified_id SERIAL PRIMARY KEY, -- Auto-incrementing unique ID across all records
    original_id INT, -- Keep the original table's id (optional, remove if not needed)
    name1 VARCHAR(255) NOT NULL,
    code1 VARCHAR(50) NOT NULL,
    code2 VARCHAR(50),
    year INT NOT NULL,
    -- Optional: Prevent duplicate records for the same person/year/code1
    UNIQUE (name1, code1, year)
);

Step 2: Insert Data from All Annual Tables #

Next, we'll pull data from each of your year-specific tables into the new master table. Use UNION ALL to combine all records (it's faster than UNION since it doesn't check for duplicates upfront—we'll handle that next if needed).

-- Insert data from each year table (repeat for all your tables)
INSERT INTO unified_data (original_id, name1, code1, code2, year)
SELECT id, name1, code1, code2, year FROM table_2007
UNION ALL
SELECT id, name1, code1, code2, year FROM table_2008
-- Add more lines here for table_2009, table_2010, etc.;

Step 3: Clean Up Duplicate Records (If Needed) #

If you have duplicate entries (e.g., the same name1, code1, and year appearing multiple times), run this query to keep only the first occurrence:

DELETE FROM unified_data
WHERE unified_id NOT IN (
    SELECT MIN(unified_id)
    FROM unified_data
    GROUP BY name1, code1, year
);

Step 4: Add Indexes to Speed Up Queries #

To make your future queries run faster, add indexes on the columns you'll most often filter or sort by (like year, name1, or code1):

-- Index for year-based queries
CREATE INDEX idx_unified_year ON unified_data(year);

-- Composite index for queries filtering by name + year (super useful for historical lookups)
CREATE INDEX idx_unified_name_year ON unified_data(name1, year);

-- Index for code1 searches
CREATE INDEX idx_unified_code1 ON unified_data(code1);

How This Helps with Queries #

Now you can run simple, clean queries across all years without juggling multiple tables. For example, to get all records for Peter across every year:

SELECT * FROM unified_data 
WHERE name1 = 'Peter' 
ORDER BY year ASC;

Alternative: Use a View (If You Can't Modify Original Tables) #

If you need to keep the original year tables intact, create a view that acts as a unified "window" to all your data. Note that views are slower than physical tables for large datasets, but they're great for quick access:

CREATE VIEW vw_unified_data AS
SELECT id, name1, code1, code2, year FROM table_2007
UNION ALL
SELECT id, name1, code1, code2, year FROM table_2008
-- Add all other year tables here;

Feel free to ask if you run into any issues with the SQL syntax or need help adapting this to your specific tables!

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