The database structure is fairly straight forward and is visually represented below. The table names in the online database differ than what is shown here for security purposes.

Database table relationship diagram

The reports presented on this website are all based on MySQL Views that concatenate various fields from multiple tables. The Views available are:

  • AHBClassicsList (Builds the list of AHB Classics)
    • Songs.SongTitle
    • Keys.KeyName
    • Songs.StartingWords
    • AHBClassics.YearSung (field not shown in the diagram above)
  • ViewHistory (Builds the complete listing of which songs were sung by each brigade by year)
    • BrigadeHistory.YearHeld
    • Brigades.BrigadeAbbr
    • Songs.SongTitle
    • Keys.KeyName
    • BrigadeHistory.CDTrackNum
    • Arrangers.Arranger
    • AsSungBy.AsSungBy
    • TrackRecordedBy.LT_Provider
    • SongStyle.SongStyle
    • Songs.SongLength
    • Songs.StartingWords
  • ViewSongHistory (Allows the user to see which brigades included a particular song)
    • BrigadeHistory.YearHeld
    • Brigades.BrigadeAbbr
    • Songs.SongTitle
    • BrigadeHistory.CDTrackNum
  • WalletCard (Reproduces the song listing for a particular brigade in the familiar Song, Key, Starting Words format)
    • Songs.SongTitle
    • Keys.KeyName
    • Songs.StartingWords
    • BrigadeHistory.YearHeld
    • Brigades.BrigadeAbbr
  • SongData (Provides all of the particulars about a given song. Who arranged it, what key is it in, etc.)
    • Songs.SongTitle
    • Keys.KeyName
    • Arrangers.Arranger
    • AsSungBy.AsSungBy
    • TrackRecordedBy.LT_Provider
    • SongStyle.SongStyle
    • Songs.SongLength
    • Songs.StartingWords

 The reports themselves are produced using the Alter Reports Joomla Extension from Alterbrains.com.