如何将包含一周七天字段的SQL表转换为第三范式（3rd Normal Form）

Hey there! Let's walk through this clearly since you're new to database normalization—no confusing jargon, just straightforward steps.

First, let's understand why your current table isn't in 3NF: You've got repeated columns for each day of the week (Sunday to Saturday), which are a repeating group of data. Plus, the checkbox state doesn't depend solely on your main ID—it depends on the combination of the user and the specific day. That violates 3NF's rules of eliminating redundant data and ensuring all non-key columns depend only on the primary key.

The Short Answer #

Yes, you should restructure your tables—but not one table per day. Instead, split your data into two focused tables: one for user account info, and another for tracking each user's checkbox status per day. This is the standard way to fix your normalization issue.

Step 1: Create a users Table (Core Account Data) #

This table will store only the unique, user-specific information that doesn't change per day:

• user_id: INT (auto-increment, primary key) – this will be the unique identifier for each user
• username: VARCHAR(50) (unique, non-null) – no duplicate usernames allowed
• password: VARCHAR(255) (non-null) – pro tip: always store hashed passwords, never plain text!

Step 2: Create a user_schedule Table (Daily Checkbox Status) #

This table will link each user to their checkbox status for each day. It eliminates those repeated day columns by using a single column to represent the day:

• (Option 1: Composite Primary Key)

  • user_id: INT (non-null, foreign key linking to users.user_id)
  • day_of_week: ENUM('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') (non-null)
  • is_checked: TINYINT(1) (0 = unchecked, 1 = checked, default 0)
  • Set user_id + day_of_week as the composite primary key to ensure one entry per user per day.

• (Option 2: Single Auto-Increment Primary Key)

  • schedule_id: INT (auto-increment, primary key)
  • user_id: INT (non-null, foreign key linking to users.user_id)
  • day_of_week: ENUM(...) (same as above)
  • is_checked: TINYINT(1) (same as above)

Why This Works for 3NF #

• Single Purpose: Each table handles one job—users manages accounts, user_schedule manages daily statuses.
• No Redundancy: No more duplicate day columns; each user's daily status is stored as a single row.
• No Transitive Dependencies: All non-key columns depend directly on the primary key (e.g., is_checked depends on the combination of user_id and day_of_week).

How to Do This in phpMyAdmin #

1. Create the users Table:

   • Open phpMyAdmin, select your database, click "New".
   • Enter table name users, set number of fields to 3, click "Go".
   • Define each field:
     • user_id: Type = INT, check "A_I" (auto-increment), set as Primary Key.
     • username: Type = VARCHAR(50), check "Unique" and "Not Null".
     • password: Type = VARCHAR(255), check "Not Null".
   • Save the table.

2. Create the user_schedule Table:

   • Click "New" again, enter table name user_schedule, set fields to 3 (or 4 if using schedule_id).
   • Define fields:
     • For composite key: Add user_id (INT, Not Null), day_of_week (ENUM, select the 7 days, Not Null), is_checked (TINYINT(1), Default 0).
     • Go to the "Structure" tab, select both user_id and day_of_week, click "Primary" to set the composite key.
     • Add a foreign key: In the "Structure" tab, click "Relation View", set user_id to reference users.user_id.
   • Save the table.

3. Migrate Your Existing Data:

   • Use SQL queries to move data from your old table to the new ones. Run these in phpMyAdmin's "SQL" tab:

     -- First, copy user accounts to the users table
     INSERT INTO users (username, password)
     SELECT DISTINCT Username, Password FROM your_old_table_name;
     
     -- Then copy daily checkbox statuses to user_schedule
     INSERT INTO user_schedule (user_id, day_of_week, is_checked)
     SELECT u.user_id, 'Sunday', o.Sunday FROM your_old_table_name o
     JOIN users u ON o.Username = u.Username
     UNION ALL
     SELECT u.user_id, 'Monday', o.Monday FROM your_old_table_name o
     JOIN users u ON o.Username = u.Username
     UNION ALL
     SELECT u.user_id, 'Tuesday', o.Tuesday FROM your_old_table_name o
     JOIN users u ON o.Username = u.Username
     UNION ALL
     SELECT u.user_id, 'Wednesday', o.Wednesday FROM your_old_table_name o
     JOIN users u ON o.Username = u.Username
     UNION ALL
     SELECT u.user_id, 'Thursday', o.Thursday FROM your_old_table_name o
     JOIN users u ON o.Username = u.Username
     UNION ALL
     SELECT u.user_id, 'Friday', o.Friday FROM your_old_table_name o
     JOIN users u ON o.Username = u.Username
     UNION ALL
     SELECT u.user_id, 'Saturday', o.Saturday FROM your_old_table_name o
     JOIN users u ON o.Username = u.Username;
     

   • Replace your_old_table_name with the name of your original table.

Final Notes #

Once you've migrated the data, you can safely drop your old table. This structure will make it easier to maintain your database (e.g., adding new status types later won't require adding more columns) and reduces the chance of inconsistent data.

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