CREATE TABLE submitter_strings (
    submitterstrid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    submitter VARCHAR(60) NOT NULL,
    submitcounter INTEGER UNSIGNED NOT NULL DEFAULT '1'
);
CREATE UNIQUE INDEX idu_submitter on submitter_strings (submitter);

CREATE TABLE artist_strings (
    artiststrid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    artist VARCHAR(60) NOT NULL
);
CREATE UNIQUE INDEX idu_artist on artist_strings (artist);

CREATE TABLE album_sampler_strings (
    albumsamplerstrid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    albumsampler VARCHAR(60) NOT NULL
);
CREATE UNIQUE INDEX idu_album on album_sampler_strings (albumsampler);

CREATE TABLE song_strings (
    songstrid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    song VARCHAR(120) NOT NULL
);
CREATE UNIQUE INDEX idu_song on song_strings (song);

CREATE TABLE genre_strings (
    genrestrid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    genre VARCHAR(30) NOT NULL
);
CREATE UNIQUE INDEX idu_genre on genre_strings (genre);

CREATE TABLE song2artist ( 
    songstrid INTEGER UNSIGNED NOT NULL,
    artiststrid INTEGER UNSIGNED NOT NULL,
    playtime INTEGER UNSIGNED NOT NULL,
    playtimevotes INTEGER UNSIGNED NOT NULL DEFAULT '1'
);
ALTER TABLE song2artist ADD CONSTRAINT pk_song2artist PRIMARY KEY (songstrid,artiststrid,playtime);
ALTER TABLE song2artist ADD CONSTRAINT fk_s2a_songstrid FOREIGN KEY (songstrid) REFERENCES song_strings (songstrid);
ALTER TABLE song2artist ADD CONSTRAINT fk_s2a_artiststrid FOREIGN KEY (artiststrid) REFERENCES artist_strings (artiststrid);

CREATE TABLE artist2album (
    artiststrid INTEGER UNSIGNED NOT NULL,
    albumsamplerstrid INTEGER UNSIGNED NOT NULL
);
ALTER TABLE artist2album ADD CONSTRAINT pk_artist2album PRIMARY KEY (artiststrid,albumsamplerstrid);
ALTER TABLE artist2album ADD CONSTRAINT fk_a2a_artiststrid FOREIGN KEY (artiststrid) REFERENCES artist_strings (artiststrid);
ALTER TABLE artist2album ADD CONSTRAINT fk_a2a_albumsamplerstrid FOREIGN KEY (albumsamplerstrid) REFERENCES album_sampler_strings (albumsamplerstrid);

CREATE TABLE song2artist2album2track (
    songstrid INTEGER UNSIGNED NOT NULL,
    artiststrid INTEGER UNSIGNED NOT NULL,
    albumsamplerstrid INTEGER UNSIGNED NOT NULL,
    track INTEGER UNSIGNED NOT NULL
);
ALTER TABLE song2artist2album2track ADD CONSTRAINT pk_song2artist2album2track PRIMARY KEY (songstrid,artiststrid,albumsamplerstrid,track);
ALTER TABLE song2artist2album2track ADD CONSTRAINT fk_s2a2a2t_songstrid FOREIGN KEY (songstrid) REFERENCES song_strings (songstrid);
ALTER TABLE song2artist2album2track ADD CONSTRAINT fk_s2a2a2t_artiststrid FOREIGN KEY (artiststrid) REFERENCES artist_strings (artiststrid);
ALTER TABLE song2artist2album2track ADD CONSTRAINT fk_s2a2a2t_albumsamplerstrid FOREIGN KEY (albumsamplerstrid) REFERENCES album_sampler_strings (albumsamplerstrid);
ALTER TABLE song2artist2album2track ADD CONSTRAINT fk_s2a2a2t_song2artistid FOREIGN KEY (songstrid,artiststrid) REFERENCES song2artist (songstrid,artiststrid);

CREATE TABLE song2artist2genrevotes (
    songstrid INTEGER UNSIGNED NOT NULL,
    artiststrid INTEGER UNSIGNED NOT NULL,
    genrestrid INTEGER UNSIGNED NOT NULL,
    genrevotes INTEGER UNSIGNED NOT NULL DEFAULT '1'
);
ALTER TABLE song2artist2genrevotes ADD CONSTRAINT pk_song2artist2genrevotes PRIMARY KEY (songstrid,artiststrid,genrestrid);
ALTER TABLE song2artist2genrevotes ADD CONSTRAINT fk_s2a2gvotes_songstrid FOREIGN KEY (songstrid) REFERENCES song_strings (songstrid);
ALTER TABLE song2artist2genrevotes ADD CONSTRAINT fk_s2a2gvotes_artiststrid FOREIGN KEY (artiststrid) REFERENCES artist_strings (artiststrid);
ALTER TABLE song2artist2genrevotes ADD CONSTRAINT fk_s2a2gvotes_genrestrid FOREIGN KEY (genrestrid) REFERENCES genre_strings (genrestrid);
ALTER TABLE song2artist2genrevotes ADD CONSTRAINT fk_s2a2gvotes_song2artistid FOREIGN KEY (songstrid,artiststrid) REFERENCES song2artist (songstrid,artiststrid);

CREATE TABLE song2artist2yearevotes (
    songstrid INTEGER UNSIGNED NOT NULL,
    artiststrid INTEGER UNSIGNED NOT NULL,
    ayear INTEGER UNSIGNED NOT NULL,
    yearvotes INTEGER UNSIGNED NOT NULL DEFAULT '1'
);
ALTER TABLE song2artist2yearevotes ADD CONSTRAINT pk_song2artist2yearvotes PRIMARY KEY (songstrid,artiststrid,ayear);
ALTER TABLE song2artist2yearevotes ADD CONSTRAINT fk_s2a2yvotes_songstrid FOREIGN KEY (songstrid) REFERENCES song_strings (songstrid);
ALTER TABLE song2artist2yearevotes ADD CONSTRAINT fk_s2a2yvotes_artiststrid FOREIGN KEY (artiststrid) REFERENCES artist_strings (artiststrid);

CREATE TABLE cds (
    cdid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    discid VARCHAR(8) NOT NULL,
    albumsamplerstrid INTEGER UNSIGNED NOT NULL,
    playtime INTEGER UNSIGNED NOT NULL,
    submitterstrid  INTEGER UNSIGNED NOT NULL
);
CREATE UNIQUE INDEX idu_cds on cds (discid,albumsamplerstrid);
ALTER TABLE cds ADD CONSTRAINT fk_cds_albumsamplerstrid FOREIGN KEY (albumsamplerstrid) REFERENCES album_sampler_strings (albumsamplerstrid);
ALTER TABLE cds ADD CONSTRAINT fk_cds_submitterstrid FOREIGN KEY (submitterstrid) REFERENCES submitter_strings (submitterstrid);

CREATE TABLE cds2yearvotes (
    cdid INTEGER UNSIGNED NOT NULL,
    ayear SMALLINT UNSIGNED NOT NULL,
    yearvotes INTEGER UNSIGNED NOT NULL DEFAULT '1'
);
ALTER TABLE cds2yearvotes ADD CONSTRAINT pk_cds2yearvotes PRIMARY KEY (cdid,ayear);
ALTER TABLE cds2yearvotes ADD CONSTRAINT fk_c2yvotes_cdid FOREIGN KEY (cdid) REFERENCES cds (cdid);

CREATE TABLE cds2genrevotes (
    cdid INTEGER UNSIGNED NOT NULL,
    genrestrid INTEGER UNSIGNED NOT NULL,
    genrevotes INTEGER UNSIGNED NOT NULL DEFAULT '1'
);
ALTER TABLE cds2genrevotes ADD CONSTRAINT pk_cds2genrevotes PRIMARY KEY (cdid,genrestrid);
ALTER TABLE cds2genrevotes ADD CONSTRAINT fk_c2gvotes_cdid FOREIGN KEY (cdid) REFERENCES cds (cdid);
ALTER TABLE cds2genrevotes ADD CONSTRAINT fk_c2gvotes_genrestrid FOREIGN KEY (genrestrid) REFERENCES genre_strings (genrestrid);

CREATE TABLE cdtracks (
    cdid INTEGER UNSIGNED NOT NULL,
    songstrid INTEGER UNSIGNED NOT NULL,
    artiststrid INTEGER UNSIGNED NOT NULL,    
    track SMALLINT UNSIGNED NOT NULL,
    offset INTEGER UNSIGNED NOT NULL
);
ALTER TABLE cdtracks ADD CONSTRAINT pk_cdtracks PRIMARY KEY (cdid,songstrid,artiststrid,track);
ALTER TABLE cdtracks ADD CONSTRAINT fk_cdtracks_songstrid FOREIGN KEY (songstrid) REFERENCES song_strings (songstrid);
ALTER TABLE cdtracks ADD CONSTRAINT fk_cdtracks_artiststrid FOREIGN KEY (artiststrid) REFERENCES artist_strings (artiststrid);
ALTER TABLE cdtracks ADD CONSTRAINT fk_cdtracks_song2artistid FOREIGN KEY (songstrid,artiststrid) REFERENCES song2artist (songstrid,artiststrid);