Best Practices

This is it - the last stop on the 33-lesson MySQL track, and rather than teaching anything new, this page pulls the most important habits from everything before it into one checklist. Every point below links back to the lesson that covers it in full depth, with a short StreamHub example and a checklist you can genuinely run your own schema against.

1. Give Every Table a Real Primary Key, and Think Through Foreign Keys

CREATE TABLE reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  movie_id INT NOT NULL,
  rating TINYINT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE
);

Covered in full in Primary & Foreign Keys.

• Every table has a primary key - never rely on "row order" to identify a record
• Every foreign key has a deliberately chosen ON DELETE behavior (CASCADE, RESTRICT, or SET NULL), not whatever MySQL happens to default to
• You can explain, in one sentence, what happens to reviews and watchlist rows if a user or movie is deleted

2. Choose the Right Data Type for the Job

CREATE TABLE movies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  release_year YEAR NOT NULL,
  duration_minutes SMALLINT NOT NULL
) CHARACTER SET utf8mb4;

Covered in Data Types and Creating & Managing Databases.

• DECIMAL for anything resembling money - never FLOAT or DOUBLE
• utf8mb4 as the character set, so titles, usernames, and comments in any language (including emoji) store correctly
• The narrowest DATE/TIME type that actually fits - YEAR for a release year, not a full DATETIME

3. Let Constraints Do Real Work

ALTER TABLE users
  ADD CONSTRAINT unique_username UNIQUE (username),
  MODIFY email VARCHAR(255) NOT NULL;

ALTER TABLE reviews
  ADD CONSTRAINT valid_rating CHECK (rating BETWEEN 1 AND 5);

Covered in Constraints.

• NOT NULL on any column that should never be missing
• UNIQUE on anything that must never duplicate, like a username or email
• CHECK constraints for simple rules (like a rating staying between 1 and 5) instead of trusting application code alone to keep bad data out

4. Look Before You Change, and Wrap Risky Changes in a Transaction

-- Run this first, always, before the UPDATE/DELETE below
SELECT * FROM reviews WHERE user_id = 42;

START TRANSACTION;
DELETE FROM reviews WHERE user_id = 42;
-- check the row count matches what you expected, then:
COMMIT;

Covered in UPDATE & DELETE and Transactions & ACID.

• Every UPDATE/DELETE you run in production was previewed as a SELECT with the same WHERE clause first
• Risky changes happen inside START TRANSACTION, so a mistake is a ROLLBACK away from undone, not a support ticket
• You never run a bare DELETE or UPDATE without a WHERE clause by accident

5. Index the Columns You Actually Filter, Sort, and Join By

CREATE INDEX idx_reviews_movie_id ON reviews(movie_id);

EXPLAIN SELECT * FROM reviews WHERE movie_id = 42;

Covered in Indexes and Query Optimization & EXPLAIN.

• Columns used in frequent WHERE, JOIN, and ORDER BY clauses have an index
• EXPLAIN gets run against slow queries instead of guessing at the cause
• Indexes exist on purpose, not by accident - every index also slows down writes a little, so they're not free

6. Normalize on Purpose, Denormalize as a Deliberate Tradeoff

-- Normalized: genre names live once, in their own table
SELECT m.title, g.name
FROM movies m
JOIN movie_genres mg ON mg.movie_id = m.id
JOIN genres g ON g.id = mg.genre_id;

Covered in Database Normalization.

• Redundant, driftable data (like a movie's genre name copy-pasted onto every row) is normalized out by default
• If you do denormalize for performance, it's written down somewhere and done on purpose - not an accident of skipped design
• A junction table like movie_genres exists wherever a genuine many-to-many relationship does

7. Never Build a Query by Concatenating Raw User Input

SELECT * FROM movies WHERE release_year > ?;
-- the ? is filled in by a bound parameter, never by string concatenation

Covered in Security Best Practices and Connecting from Application Code.

• Every query touching user input uses a parameterized query, a prepared statement, or an ORM - never string concatenation
• This holds even for "internal" tools and admin panels, not just public-facing forms

8. Give Application Accounts Only the Privileges They Need

GRANT SELECT, INSERT, UPDATE, DELETE ON streamhub.* TO 'streamhub_app'@'localhost';

Covered in Users & Privileges.

• The application never connects as root, ever, in any environment
• No account holds ALL PRIVILEGES unless it genuinely needs every single one of them
• You could explain exactly why each grant on the application's account exists

9. Back Up Regularly, Store Backups Separately, and Test Restores

mysqldump -u root -p streamhub > streamhub_backup.sql

Covered in Backup & Restore.

• Backups run on a real schedule, not "whenever someone remembers"
• At least one copy lives somewhere other than the live database server
• A restore has actually been tested at least once - a backup nobody has ever restored from is a guess, not a safety net

10. Use Stored Procedures and Triggers Sparingly and Deliberately

CREATE TRIGGER trg_watchlist_added
BEFORE INSERT ON watchlist
FOR EACH ROW
SET NEW.added_date = NOW();

Covered in Stored Procedures & Functions and Triggers.

• Every trigger and stored procedure earns its place - it does something genuinely awkward to do any other way
• Anyone reviewing application logic knows to also check the database for hidden triggers and procedures
• Logic isn't split between application code and the database just because it was convenient in the moment

Congratulations on making it through all 33 lessons of this track. Along the way, using StreamHub as one consistent, running example, you've built a genuine mental model of MySQL from the ground up - designing a schema with real keys and constraints, writing the full range of the SQL query language, combining tables with joins and subqueries, tuning performance with indexes and EXPLAIN, keeping data consistent with transactions, pushing logic into stored procedures and triggers where it truly belongs, and finally, the administration and security skills - users, privileges, backups, and safe application code - that it takes to run a real database with confidence rather than crossed fingers. That's not a small thing, and it's the same foundation every other track on this site leans on the moment it needs to talk about "the database."

From here, the Next button below leads back to the Database hub, where MariaDB, MongoDB, Oracle, and SQLite are waiting to be explored as those tracks become available - each one a different angle on the same problem this track just walked you through end to end.