天气信息管理系统单表数据库规范化：多表拆分方法咨询

Awesome question! Normalizing your weather database will make it way more maintainable, cut down on redundant data, and make future tweaks (like adding new weather metrics or city details) a breeze. Let's break this down step by step, focusing on 3rd Normal Form (3NF)—the standard for most practical, scalable databases.

Step 1: Spot the Redundant & Dimension Data #

Your current single table has two distinct types of data:

• Dynamic readings: Values that change daily per city (humidity, temperature, heat index, pressure, wind speed)
• Dimension data: Static or semi-static values that repeat across records (city name, wind direction, UV index levels)

The goal is to pull these dimension values into their own tables so we don’t repeat the same text/descriptions over and over.

Step 2: Build Your Dimension Tables #

These tables will store static data and assign unique IDs, which we’ll link back to our core readings table for consistency.

1. Cities Table #

Since city names repeat across dates, giving each city a unique ID eliminates redundancy. This also lets you add extra city details later (like latitude/longitude) without disrupting your readings data.

CREATE TABLE cities (
    city_id INT PRIMARY KEY AUTO_INCREMENT,
    city_name VARCHAR(100) UNIQUE NOT NULL,
    latitude DECIMAL(9,6) NULL, -- Optional, but useful for location-based features
    longitude DECIMAL(9,6) NULL -- Optional
);

2. Wind Directions Table #

Wind directions are a fixed set of values (North, Northwest, etc.). Storing them separately ensures no typos (like "Nothwest") and lets you add abbreviations or descriptions if needed.

CREATE TABLE wind_directions (
    direction_id INT PRIMARY KEY AUTO_INCREMENT,
    direction_name VARCHAR(50) UNIQUE NOT NULL,
    direction_abbrev VARCHAR(10) NULL -- e.g., "NW" for "Northwest"
);

3. UV Indexes Table #

UV indexes often come with standard risk descriptions (0-2 = Low, 3-5 = Moderate, etc.). Even if you only need the numeric value now, this table gives you room to add context later without changing your core readings.

CREATE TABLE uv_indexes (
    uv_id INT PRIMARY KEY AUTO_INCREMENT,
    uv_value INT UNIQUE NOT NULL,
    uv_description VARCHAR(100) NULL -- e.g., "Moderate risk of harm from unprotected sun exposure"
);

Step 3: Create the Core Weather Readings Table #

This is your "fact table"—it stores all dynamic daily readings, linked to dimension tables via foreign keys. We’ll keep a unique constraint on city_id + reading_date to match your original composite primary key logic.

CREATE TABLE weather_readings (
    reading_id INT PRIMARY KEY AUTO_INCREMENT,
    city_id INT NOT NULL,
    reading_date DATE NOT NULL,
    humidity DECIMAL(5,2) NOT NULL,
    temperature DECIMAL(5,2) NOT NULL, -- Adjust scale for your unit (C/F)
    heat_index DECIMAL(5,2) NOT NULL,
    pressure DECIMAL(6,2) NOT NULL,
    wind_speed DECIMAL(5,2) NOT NULL, -- e.g., km/h or mph
    direction_id INT NOT NULL,
    uv_id INT NOT NULL,
    FOREIGN KEY (city_id) REFERENCES cities(city_id),
    FOREIGN KEY (direction_id) REFERENCES wind_directions(direction_id),
    FOREIGN KEY (uv_id) REFERENCES uv_indexes(uv_id),
    UNIQUE KEY unique_city_date (city_id, reading_date) -- Prevent duplicate readings for the same city+date
);

Why This Schema Is Better #

• No redundancy: You won’t repeat city names, wind directions, or UV descriptions across hundreds/thousands of records.
• Data consistency: Typos in city names or wind directions are impossible—you pick values directly from dimension tables.
• Easy to extend: Want to add "precipitation" later? Just add a column to weather_readings. Want to track city population? Add it to cities without touching your weather data.
• Faster queries: Indexes on foreign keys make joins between tables efficient, even with large datasets.

Optional Tweaks #

• If you don’t need UV index descriptions, skip the uv_indexes table and store the numeric uv_value directly in weather_readings.
• If wind directions are sometimes free-form (not just standard compass directions), skip the wind_directions table and store the direction as a string—only do this if consistency isn’t a top priority.

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