Music categorization database

Question

Recently I've tried to teach myself basic things about MySQL databases, however I received some help on Stack Overflow with creating a good database structure trying to avoid redundancy as much as possible.

I finally came up with the database structure below and would like to know if that's the way to go and if not, what you would improve/change to it.

CREATE TABLE artists (
    artist_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    artist_name varchar(100) UNIQUE,
    artist_aka varchar(255) UNIQUE
) ENGINE = 'InnoDB';

CREATE TABLE labels (
    label_id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    label_name varchar(100) UNIQUE,
    label_aka varchar(255) UNIQUE
) ENGINE = 'InnoDB';

CREATE TABLE producers (
    producer_id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    producer_forename varchar(100),
    producer_nickname varchar(100) UNIQUE,
    producer_lastname varchar(100)
) ENGINE = 'InnoDB';

CREATE TABLE years (
    year_id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    year_value varchar(4) UNIQUE
) ENGINE = 'InnoDB';

CREATE TABLE genres (
    genre_id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    genre_name varchar(10) UNIQUE
) ENGINE = 'InnoDB';

CREATE TABLE flags (
    flag_id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    flag_name varchar(12) UNIQUE
) ENGINE = 'InnoDB';

CREATE TABLE tags (
    tag_id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tag_name varchar(16) UNIQUE
) ENGINE = 'InnoDB';

CREATE TABLE sources (
    source_id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    source_name varchar(30) UNIQUE
) ENGINE = 'InnoDB';

CREATE TABLE riddims (
    riddim_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    riddim_name varchar(40) UNIQUE,
    riddim_aka varchar(255) UNIQUE,
    genre_fk TINYINT,
    youtube varchar(11) UNIQUE,
    image varchar(11) UNIQUE,
    FOREIGN KEY (genre_fk) REFERENCES genres(genre_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = 'InnoDB';

CREATE TABLE tunes (
    tune_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    riddim_fk INT DEFAULT NULL,
    artist_fk INT DEFAULT NULL,
    tune_name varchar(60) DEFAULT NULL,
    tune_aka varchar(255) DEFAULT NULL,
    label_fk SMALLINT DEFAULT NULL,
    producer_fk SMALLINT DEFAULT NULL,
    year_fk TINYINT DEFAULT NULL,   
    lyrics TEXT DEFAULT NULL,
    flag_fk TINYINT,
    tag_fk SMALLINT,
    source_fk TINYINT,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (riddim_fk) REFERENCES riddims(riddim_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (artist_fk) REFERENCES artists(artist_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (producer_fk) REFERENCES producers(producer_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (year_fk) REFERENCES years(year_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (flag_fk) REFERENCES flags(flag_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (tag_fk) REFERENCES tags(tag_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (source_fk) REFERENCES sources(source_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = 'InnoDB';

Later the website will contain information about "riddims" (rythms), artists that have a song on that riddim, songnames, release years etcetera. I guess the best way to show what its supposed to do is showing you a first, unfinished version of the website.

Answer 1

Just a couple of suggestions.

First, artists and producers are both persons and a person can be both an artist and a producer (even on their own songs).

Second, more than one persons can work together on a song, but in lots of different roles (backup musician, backup singer, "featuring", "uncredited", or as a member of a groups). So performers and roles would probably look something like:

CREATE TABLE roles (
    role_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    description varchar(100) not null
) ENGINE = 'INNODB';

CREATE TABLE performers (
    performer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tune_fk INT NOT NULL,
    person_fk INT NOT NULL,
    role_fk INT NOT NULL,
    FOREIGN KEY (tune_fk) REFERENCES tunes(tune_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (person_fk) REFERENCES persons(person_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (role_fk) REFERENCES roles(role_id) ON DELETE SET NULL ON UPDATE CASCADE,
) ENGINE = 'InnoDB';