MySQL Database Specification

song_info

-- The song_info table describes the base information of the song.
create table song_info (
  song_id integer unsigned not null auto_increment,
  ccli_song_number integer unsigned,
  copyright_notice char(50),
  lowest_note_interval tinyint,
  highest_note_interval tinyint,
  mode enum('ionian',  -- major
            'dorian',
            'phrygian',
            'lydian',
            'mixolydian',
            'aeolian',  -- minor
            'locrian'),
  user_id integer unsigned references user_info,

  primary key (song_id),
  unique (ccli_song_number),
  index (highest_note_interval,lowest_note_interval)
);

author_info

-- The author_info table describes song authors.
create table author_info (
  author_id integer unsigned not null auto_increment,
  name varchar(50) not null,

  primary key (author_id),
  fulltext (name)
);

theme_info

-- The theme_info table describes themes.
create table theme_info (
  theme_id integer unsigned not null auto_increment,
  name varchar(20) not null,

  primary key (theme_id),
  index (name)
);

singer_info

-- The singer_info table describes song leaders and other vocalists.
create table singer_info (
  singer_id integer unsigned not null auto_increment,
  name varchar(50) not null,
  highest_midi_note_number tinyint unsigned,
  lowest_midi_note_number tinyint unsigned,

  primary key (singer_id),
  index (name)
);

style_info

-- The style_info table describes performance styles.
create table style_info (
  style_id integer unsigned not null auto_increment,
  tempo_beats_per_minute tinyint unsigned,
  beats_per_measure tinyint unsigned,
  beats_per_whole_note tinyint unsigned,
  beats_per_tempo_beat tinyint unsigned,

  primary key (style_id),
  index (tempo_beats_per_minute),
  index (beats_per_measure, beats_per_whole_note, beats_per_tempo_beat)
);

genre_info

-- The genre_info table describes musical genres.
create table genre_info (
  genre_id integer unsigned not null auto_increment,
  name varchar(20) not null,

  primary key (genre_id),
  index (name)
);

performance_info

-- The performance_info table describes performances.
create table performance_info (
  performance_id integer unsigned not null auto_increment,
  perf_date date not null,
  singer_id integer unsigned references singer_info,
  type enum('congregational', 'special'),

  primary key (performance_id),
  index (perf_date),
  index (singer_id, perf_date)
);

user_info

-- The user_info table describes web-interface users of the database.
create table user_info (
  user_id integer unsigned not null auto_increment,
  username char(16) not null,
  password_hash char(32) not null,
  full_name char(50),
  email_address char(50),
  telephone_number char(20),
  singer_id integer unsigned references singer_info,
  privs set('search',
            'modify own user history',
            'modify own performance history',
            'modify performances',
            'modify songs',
            'modify singers',
            'modify users') not null,

  primary key (user_id),
  unique (username),
  unique (singer_id)
);

song_title

-- The song_title table associates songs with their titles.
create table song_title (
  song_id integer unsigned not null references song_info,
  title varchar(50) not null,
  is_preferred_title tinyint(1) not null,
    -- non-0 means yes, only one preferred title per song_id

  index (song_id, is_preferred_title),
  fulltext (title)
);

song_line

-- The song_line table associates songs with their key lines.
create table song_line (
  song_id integer unsigned not null references song_info,
  line varchar(100) not null,

  index (song_id),
  fulltext (line)
);

song_author

-- The song_author table associates songs with their authors.
create table song_author (
  song_id integer unsigned not null references song_info,
  author_id integer unsigned not null references author_info,

  index (song_id),
  index (author_id)
);

song_theme

-- The song_theme table associates songs with themes.
create table song_theme (
  song_id integer unsigned not null references song_info,
  theme_id integer unsigned not null references theme_info,
    -- maximum of one of each theme_id per song_id, like a set

  index (song_id),
  index (theme_id)
);

song_lyric

-- The song_lyric table associates songs with lyrics.
create table song_lyric (
  song_id integer unsigned not null references song_info,
  lyric text not null,

  unique (song_id),
  fulltext (lyric)
);
-- Note that even though song_id is unique, this information is put in
-- a table seperate from the song_info table in order to avoid
-- variable row lengths in the song_info table, for efficiency.

song_tonic

-- The song_tonic table associates songs with the keys in which they are written.
create table song_tonic (
  song_id integer unsigned not null references song_info,
  tonic enum('C',
             'D-flat',
             'D',
             'E-flat',
             'E',
             'F',
             'G-flat',
             'G',
             'A-flat',
             'A',
             'B-flat',
             'B') not null,

  unique (song_id, tonic),
  index (tonic)
);

song_style

-- The song_style table associates songs with their performance styles.
create table song_style (
  song_id integer unsigned not null references song_info,
  style_id integer unsigned not null references style_info,

  unique (song_id, style_id),
  index (style_id)
);

song_performance

-- The song_performance table associates songs with performance dates.
create table song_performance (
  song_id integer unsigned not null references song_info,
  performance_id integer unsigned not null references performance_info,
  tonic enum('C',
             'D-flat',
             'D',
             'E-flat',
             'E',
             'F',
             'G-flat',
             'G',
             'A-flat',
             'A',
             'B-flat',
             'B'),
  style_id integer unsigned references style_info,
  perf_order tinyint,
    -- perf_order is unique for each performance_id

  index song_tonic (song_id, tonic),
  index song_style (song_id, style_id),
  unique (performance_id, perf_order)
);

style_genre

-- The style_genre table associates performance styles with musical genres.
create table style_genre (
  style_id integer unsigned not null references style_info,
  genre_id integer unsigned not null references genre_info,
    -- maximum of one of each genre_id per style_id, like a set

  index (style_id),
  index (genre_id)
);



user_song

-- The user_song table records a user's past song views.
create table user_song (
  user_id integer unsigned not null references user_info,
  time_stamp timestamp,
  song_id integer unsigned not null references song_info,

  index (user_id, time_stamp)
);

user_performance

-- The user_performance table records a user's past performance views.
create table user_performance (
  user_id integer unsigned not null references user_info,
  time_stamp timestamp,
  performance_id integer unsigned not null references performance_info,

  index (user_id, time_stamp)
);

end

SourceForge.net Logo