6
\$\begingroup\$

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.

\$\endgroup\$
4
\$\begingroup\$

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';
\$\endgroup\$
3
  • \$\begingroup\$ I was going to add a few more details like "birthdate", "day of death" and "birthdates" to artists later on, might aswell take ur suggestion into consideration, thanks. Though my question is more about the efficiency of my database layout (maybe I didn't state that clear enough, apologies); is it a good structure to work with when each (major) table will have between 50.000 and 100.000 entries at the beginning, growing consistently per week? Is it a good structure when there's a lot of searches (for "tune", "artist" and "riddim") performed? \$\endgroup\$
    – phew
    Sep 17 '12 at 12:42
  • \$\begingroup\$ There isn't anything terribly complicated about your design and all of the links from one table to another are done through integer keys so it seems very unlikely that you will have any major performance issues. \$\endgroup\$ Sep 17 '12 at 14:09
  • \$\begingroup\$ Tahnks, I was just very unsure about this as I'm pretty new to databases. I just added your earlier suggestion and stick with that database layout now. \$\endgroup\$
    – phew
    Sep 17 '12 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.