Database Design Best Practices: Indexing, Relational Design & Datatypes — with a Music Playlist Example

Most database performance and maintainability problems begin with decisions made at the start of a project: incorrect datatypes, missing indexes, duplicated data, and poorly designed relationships.

In this article, we will cover the most important database design principles and then apply them to a practical example: a music playlist application where singers have songs, songs belong to categories, and users can create their own playlists.

1. Relational Database Design Fundamentals

Normalization Without the Academic Jargon

The practical goal of normalization is simple: do not store the same fact in multiple places.

For example, imagine storing the singer's name directly inside every row of the songs table. If the singer's name changes, you may need to update hundreds or thousands of rows. Missing even one row can create inconsistent data.

A better approach is to store the singer once inside a singers table and reference that singer from the songs table using singer_id.

• 1NF: Every column should contain one value. Avoid storing comma-separated values such as pop,rock,90s inside a single column.
• 2NF: Non-key fields should depend on the complete primary key, especially when working with composite keys.
• 3NF: Non-key fields should depend only on the primary key, not on other non-key columns.

A useful practical rule is this: if changing one real-world fact requires updating the same information in several rows, the database may not be properly normalized.

When Denormalization Makes Sense

Normalization is usually the correct starting point, but sometimes duplicating data intentionally can improve performance.

For example, instead of calculating the number of songs for a singer with COUNT() every time, a high-traffic system might maintain a songs_count column.

The important rule is: normalize first and denormalize later only when you have measured a real performance problem.

2. Choose the Right Datatypes

Datatype selection has a direct effect on storage usage, validation, indexing, and long-term scalability. Avoid automatically using VARCHAR(255) and INT for every field.

Situation	Common Mistake	Better Choice	Why
Primary keys	INT everywhere	BIGINT UNSIGNED for high-growth tables	Provides more room for future growth.
Short text	VARCHAR(255) for everything	Use realistic lengths such as VARCHAR(100) or VARCHAR(150)	Keeps validation and row sizes more appropriate.
Long text	Trying to fit everything into VARCHAR	TEXT or MEDIUMTEXT	Better for biographies, descriptions, lyrics, and other long content.
Fixed small values	Creating unnecessary lookup tables	ENUM or TINYINT where appropriate	Useful for small sets that rarely change.
Expandable categories	Hardcoding values in ENUM	A separate lookup table	New values can be added without altering the table structure.
Money	FLOAT or DOUBLE	DECIMAL(10,2)	Avoids floating-point rounding problems.
Date/time	Saving dates as strings	DATE, DATETIME, or TIMESTAMP	Allows proper filtering, sorting, and date calculations.
Boolean values	Saving yes/no as text	BOOLEAN or TINYINT(1)	Cleaner and more efficient.

The general principle is: size your columns according to realistic business requirements instead of choosing large defaults by habit.

3. Indexing: What Actually Helps

Indexes improve read performance, but they are not free. Every index requires additional storage and must also be maintained whenever records are inserted, updated, or deleted.

Therefore, indexes should be created based on real query patterns.

Good Candidates for Indexing

• Foreign key columns used frequently in joins.
• Columns commonly used in WHERE conditions.
• Columns frequently used in ORDER BY.
• Multiple columns that are commonly filtered together.

What You Should Avoid

• Indexing every column just in case.
• Indexing fields that are almost never searched or joined.
• Creating standalone indexes on very low-cardinality fields without a real need.

Composite Index Order Matters

Consider the following index:

INDEX idx_songs_singer_category (singer_id, category_id)

This index is useful when filtering by:

• singer_id
• singer_id and category_id together

However, it is generally not as useful for a query that filters only by category_id. The order of columns inside a composite index matters.

You should also verify actual query execution plans instead of guessing.

EXPLAIN
SELECT *
FROM songs
WHERE singer_id = 12
AND category_id = 3;

In MySQL, a result such as type: ALL commonly indicates a full table scan, while values such as ref or range indicate that an index is being used more effectively.

4. Practical Example: Music Playlist Database

Now we can apply these ideas to a practical application with singers, songs, categories, playlists, and users. The core relationships are straightforward: a singer has many songs, songs belong to categories, users create playlists, and playlists contain many songs through a many-to-many relationship. :contentReference[oaicite:1]{index=1}

Entity Relationships

• Singer → Songs: One-to-many
• Category → Songs: One-to-many
• User → Playlists: One-to-many
• Playlist ↔ Songs: Many-to-many

SQL Database Schema

CREATE TABLE singers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    country VARCHAR(100),
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_singers_slug (slug)
);

CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL,
    slug VARCHAR(90) NOT NULL UNIQUE
);

CREATE TABLE songs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    singer_id BIGINT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    title VARCHAR(150) NOT NULL,
    duration_seconds SMALLINT UNSIGNED NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    release_date DATE,
    play_count BIGINT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (singer_id)
        REFERENCES singers(id)
        ON DELETE CASCADE,

    FOREIGN KEY (category_id)
        REFERENCES categories(id)
        ON DELETE RESTRICT,

    INDEX idx_songs_singer_category (singer_id, category_id),
    INDEX idx_songs_title (title)
);

CREATE TABLE playlists (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    is_public TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_playlists_user (user_id)
);

CREATE TABLE playlist_song (
    playlist_id BIGINT UNSIGNED NOT NULL,
    song_id BIGINT UNSIGNED NOT NULL,
    position SMALLINT UNSIGNED DEFAULT 0,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (playlist_id, song_id),

    FOREIGN KEY (playlist_id)
        REFERENCES playlists(id)
        ON DELETE CASCADE,

    FOREIGN KEY (song_id)
        REFERENCES songs(id)
        ON DELETE CASCADE
);

5. Why These Design Choices Matter

Unique Slugs

The slug fields are unique because they may be used in URLs such as:

/artist/the-weeknd

A unique constraint prevents duplicate URLs, while indexing helps make slug-based lookups fast.

Use SMALLINT for Song Duration

A song's duration does not require a full INT. SMALLINT UNSIGNED provides more than enough room for normal song durations while consuming less space.

Use BIGINT for Play Counts

A popular song may eventually accumulate billions of plays. Using BIGINT UNSIGNED from the beginning prevents a difficult migration later.

Composite Primary Key for Playlist Songs

The playlist_song table uses:

PRIMARY KEY (playlist_id, song_id)

This means the same song cannot be inserted into the same playlist twice. The database itself enforces the rule instead of relying entirely on application code.

Intentional Delete Behavior

ON DELETE CASCADE is appropriate for playlist relationships because removing a playlist or song should also remove its corresponding pivot records.

For categories, ON DELETE RESTRICT is safer because it prevents deleting a category while songs are still assigned to it. These choices are part of the source schema design. :contentReference[oaicite:2]{index=2}

6. Laravel Migration Equivalent

Laravel makes the same database structure easy to express using migrations.

Schema::create('songs', function (Blueprint $table) {
    $table->id();

    $table->foreignId('singer_id')
        ->constrained()
        ->cascadeOnDelete();

    $table->foreignId('category_id')
        ->constrained()
        ->restrictOnDelete();

    $table->string('title', 150);
    $table->unsignedSmallInteger('duration_seconds');
    $table->string('file_path', 255);
    $table->date('release_date')->nullable();
    $table->unsignedBigInteger('play_count')->default(0);
    $table->timestamps();

    $table->index(['singer_id', 'category_id']);
});

Schema::create('playlist_song', function (Blueprint $table) {
    $table->foreignId('playlist_id')
        ->constrained()
        ->cascadeOnDelete();

    $table->foreignId('song_id')
        ->constrained()
        ->cascadeOnDelete();

    $table->unsignedSmallInteger('position')->default(0);
    $table->timestamp('added_at')->useCurrent();

    $table->primary(['playlist_id', 'song_id']);
});

7. Avoid the N+1 Query Problem in Laravel

A good database schema can still perform badly if the application queries it inefficiently. One of the most common Laravel performance problems is the N+1 query issue.

Bad Example

$songs = Song::all();

foreach ($songs as $song) {
    echo $song->singer->name;
}

Laravel may execute an additional query for the singer relationship for each song.

Better: Use Eager Loading

$songs = Song::with('singer', 'category')->get();

This dramatically reduces the number of database queries and is especially important when returning large datasets. The source example uses eager loading specifically to avoid the N+1 pattern. :contentReference[oaicite:3]{index=3}

8. Adding Songs to a Playlist

Define the many-to-many relationship inside your Laravel model:

class Playlist extends Model
{
    public function songs()
    {
        return $this->belongsToMany(Song::class)
            ->withPivot('position', 'added_at')
            ->withTimestamps();
    }
}

Then add a song using:

$playlist->songs()->syncWithoutDetaching([$songId]);

This works nicely with the composite key because it helps avoid duplicate playlist-song relationships.

9. Fetch a Playlist Efficiently

When loading a playlist, we often need the songs, singer information, and categories together.

$playlist = Playlist::with([
        'songs.singer',
        'songs.category'
    ])
    ->where('user_id', $userId)
    ->findOrFail($playlistId);

This keeps the application code clean while avoiding repeated relationship queries.

10. Database Design Checklist

• Store each real-world fact in one place.
• Avoid repeating singer, category, or user information across related rows.
• Choose datatypes based on realistic data sizes.
• Use DECIMAL instead of floating-point types for currency.
• Use foreign keys to preserve relational integrity.
• Choose CASCADE and RESTRICT behavior intentionally.
• Index columns that appear frequently in WHERE, JOIN, and ORDER BY.
• Design composite indexes according to actual query patterns.
• Use pivot tables for many-to-many relationships.
• Use composite keys when duplicate relationships should not exist.
• Use EXPLAIN to verify query performance.
• Use Laravel eager loading to avoid N+1 queries.

Final Thoughts

Good database design is not about creating the most complicated schema. It is about making deliberate decisions that keep the data consistent, queries efficient, and future changes manageable.

Start with a normalized structure. Choose appropriate datatypes. Add indexes based on real query patterns. Define relationships clearly and enforce them with foreign keys.

Then optimize only when your application actually needs it.

A well-designed schema can support a small application today and continue working effectively as the amount of data grows significantly. That is the real value of getting database design right from the beginning.