KS Project Log 13/07/25

✅ Data Cleaning & Import Process (Members + Payments)

🔄 CSV Encoding Conversion

• members.csv and payments.csv were originally saved in UTF-8, but MySQL Workbench tried to interpret them using CP949/ANSI by default, causing decoding errors.
• Converted both files to utf-8-sig (UTF-8 with BOM) format to ensure compatibility:
  • members_final_hashed_pw.csv → members_final_hashed_pw_bom.csv
  • payments.csv → payments_bom.csv

✅ MySQL Table Structure Updates

• Revised and expanded the members table structure:

  CREATE TABLE members (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      member_id VARCHAR(20) UNIQUE,
      member_name_kr VARCHAR(100),
      member_name VARCHAR(100),
      member_type VARCHAR(50),
      household_name_kr VARCHAR(100),
      household_name VARCHAR(100),
      phone VARCHAR(20),
      email VARCHAR(255),
      qr_token VARCHAR(100) UNIQUE,
      s3_url VARCHAR(255),
      expiry_date DATE,
      is_admin TINYINT(1) DEFAULT 0,
      password_hash VARCHAR(255),
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  );
  

• Created the payments table:

  CREATE TABLE payments (
      payment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      member_id VARCHAR(20),
      payee_name VARCHAR(100),
      payment_date DATE,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (member_id) REFERENCES members(member_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
  );
  

✅ Trigger Setup

• Added a trigger to automatically update members.expiry_date when a new payment is inserted:

  CREATE TRIGGER update_expiry_after_payment
  AFTER INSERT ON payments
  FOR EACH ROW
  BEGIN
    UPDATE members
    SET expiry_date = GREATEST(
                        expiry_date, 
                        DATE_ADD(NEW.payment_date, INTERVAL 1 YEAR)
                     )
    WHERE member_id = NEW.member_id
      AND (member_type IS NULL OR LOWER(member_type) != 'lifetime');
  END;
  

✅ Data Import

• MySQL Workbench Import Wizard failed due to encoding issues.
• Used command line to import CSV directly via LOAD DATA LOCAL INFILE:

  LOAD DATA LOCAL INFILE 'C:/.../members.csv'
  INTO TABLE members
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS;
  
  LOAD DATA LOCAL INFILE 'C:/.../payments.csv'
  INTO TABLE payments
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS;
  

• When foreign key constraint errors occurred, identified missing member_id values with:

  SELECT p.member_id
  FROM payments p
  LEFT JOIN members m ON p.member_id = m.member_id
  WHERE m.member_id IS NULL;
  

---

📌 Summary

• Resolved CSV encoding issues by converting to UTF-8 with BOM
• Implemented trigger for automated expiry date updates
• Successfully inserted data using CLI and LOAD DATA LOCAL INFILE
• Completed RDS import and structure update