请求对奥运会项目数据库第一范式设计方案提供反馈

Great call starting with normalization and adding the Sports table—this is a solid foundation for your Olympics database! Let’s break down specific, actionable feedback for your 1NF design, tied directly to the tables you’ve outlined:

Key 1NF Principles to Anchor Your Design #

First, remember 1NF requires three core rules:

• All columns hold atomic values (no multi-value entries like comma-separated lists)
• No repeating column groups (e.g., event_1, event_2 in the same row)
• Every row has a unique identifier (primary key)

Table-by-Table 1NF Validation & Recommendations #

1. Sports Table #

Your decision to add this table is spot-on—separating sports from events eliminates redundant data and aligns with 1NF.

• Must-have: A single-column primary key (e.g., sport_id integer auto-increment) to uniquely identify each sport.
• Atomicity check: Ensure sport_name holds only one sport per row (e.g., "Athletics", "Swimming"—not "Athletics/Swimming").
• Optional: Add atomic metadata like sport_category (e.g., "Summer", "Winter") if relevant to your project.

2. Events Table #

Linking this to Sports via a foreign key (sport_id) is critical for 1NF compliance:

• Primary key: Use event_id as the unique identifier for each individual event.
• Atomicity fix: Never store multiple events in one row. For example, split a row like "Men’s 100m/200m Sprint" into two separate rows, each with the same sport_id (for Athletics) but unique event_name values.
• Foreign key: Ensure sport_id directly references Sports.sport_id to maintain relational integrity.

3. Athletes Table #

• Primary key: Assign athlete_id as the unique identifier for each athlete (avoid using names, since duplicates are possible).
• Atomicity check: Columns like athlete_name, date_of_birth, and country_id must hold single values—no comma-separated names or multiple country entries.
• Eliminate repeating groups: If you initially had columns like event_1, event_2 for an athlete’s competitions, remove these. Instead, create a junction table (e.g., Athlete_Event) to link athletes to their events (this is still 1NF-compliant and sets you up for 2NF later).

4. Country Table #

• Primary key: Use country_id (or ISO 3166 codes like country_iso as a natural key) to uniquely identify each country.
• Atomicity: Columns like country_name should hold one country per row—no entries like "USA/Canada".

5. Medals Table #

This table is prone to 1NF violations, so pay close attention:

• Primary key: Use medal_id as a unique identifier, or a composite key (athlete_id + event_id + medal_type) if you want to enforce one medal per athlete per event.
• Atomicity fix: Never store multiple medals in a single row. For example, split a row like athlete_id: 123, event_ids: "45,67", medals: "Gold,Silver" into two separate rows:
  • Row 1: athlete_id: 123, event_id: 45, medal_type: "Gold"
  • Row 2: athlete_id: 123, event_id: 67, medal_type: "Silver"
• Foreign keys: Link athlete_id to Athletes.athlete_id and event_id to Events.event_id to keep relationships intact.

Quick 1NF Checklist to Verify Your Design #

• No column contains multiple values (comma-separated lists, slashes, etc.)
• No repeating column groups (e.g., medal_1, medal_2)
• Every table has a primary key
• All foreign keys correctly reference their parent tables

You’re already on the right track by splitting Sports and Events—this structure will make it easy to move to 2NF and 3NF next if needed.

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