What are some potential pitfalls of storing minute-by-minute goals in separate columns in a MySQL database?

Storing minute-by-minute goals in separate columns in a MySQL database can lead to a large number of columns and inefficient queries. A better approach would be to store the minute-by-minute goals in a separate table with a foreign key referencing the main table, allowing for a more flexible and scalable solution. 

// Create a new table to store minute-by-minute goals
$sql = "CREATE TABLE minute_goals (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    main_id INT(11),
    minute INT(11),
    goal_count INT(11),
    FOREIGN KEY (main_id) REFERENCES main_table(id)
)";

// Insert minute-by-minute goals into the new table
$sql = "INSERT INTO minute_goals (main_id, minute, goal_count) 
        VALUES (:main_id, :minute, :goal_count)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':main_id', $main_id);
$stmt->bindParam(':minute', $minute);
$stmt->bindParam(':goal_count', $goal_count);
$stmt->execute();

// Retrieve minute-by-minute goals using a JOIN query
$sql = "SELECT main_table.*, minute_goals.minute, minute_goals.goal_count
        FROM main_table
        LEFT JOIN minute_goals ON main_table.id = minute_goals.main_id
        WHERE main_table.id = :main_id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':main_id', $main_id);
$stmt->execute();

Keywords

MySQL database design normalization denormalization performance bottlenecks

Related Questions