What is the best way to organize chords/keys in a database (details below)?

Question

I am working on a project that involves putting together a database of chords/keys, and I'm wondering what you guys think is the best way to categorize each chord/key. For example, if I want users to input a chord into the database using several dropdown menus, what menus/choices do I need? The goal is to make the information easy to group/sort/filter in meaningful ways, however, I am far from an expert on music theory, so I would like to know what you think are the most meaningful ways to categorize/break down the data.

Here are the menus that I have so far, do these make sense? Am I even close?

• Root
  • A
  • B
  • C
  • etc.
• Accidental
  • Sharp
  • Natural
  • Flat
• Quality
  • Major
  • Minor
  • Diminished
• [Unsure of terminology here]
  • 3rd
  • 5th
  • 7th
  • etc.

Thanks in advance for any suggestions/guidance!

Answer 1

I'll give you the off-the-cuff perspective of a guy who got his degree in piano and then spent the next 30 years in software design. :)

Let me say first that it is important that you understand that the manner in which the data are stored is not the same thing as the manner in which they are displayed. It looks like you have a pretty good start on the way that they are displayed.

Now, are you asking to have a user fill up a database with chords, or put in the characteristics of a chord and get output as to how the chord is spelled? The first is what you're implying, but the second might be more useful. I'll assume the second, and give you my first impression of how I would go about it.

I would consider starting this way. Allow the user to type in the letter A-G. Have two option buttons (radio buttons) in a set, one for sharp and one for flat. Then use another set of option buttons for major, minor, dimished, and augmented. Finally, have a check box for 7th.

Leave other chords until you get these basics working. This will give you all of the triads and all the 7th chords.

Now, there are two ways to store the data. A "flat" database would simply have a record for each different chord, which would have the spelling of the chord. A "normalized" database would have three separate tables: one for chords, one for notes, and one for which notes go with which chords. Notes (a table called Notes) has two fields: a key (just a number, a different number for each record or row in the table) and a note name. This table contains all the different possible notes in the scale, including all the enharmonic ones. (Enharmonic notes are notes that are spelled differently but are the same pitch, for example F# and Gb.) Include all the double sharps and flats as well; you'll need them for augmented and diminished chords with a sharp or flat root. For example, Ab diminished is Ab-Cb-Ebb, and G# augmented is G#-B#-Dx. Then, store all the chords in a Chords table. (Skip chords whose root is a double sharp or flat; they theoretically exist but come up rarely if at all in practice) Each record in the Chords table has two fields: a key and the name of the chord. Finally, you tie these together with a ChordNotes table. This one has three fields: the key of the Chords table and the key of the Notes table, and also a number which shows the place in the chord for that note, from bottom to top. For example, in C major, C is 1, E is 2, and G is 3.

I'll give an example to show how this works. Suppose your user has selected C major as the chord. You look up C major in the chords table; let's say its key (table key, not musical key) is 14. You then go to the ChordNotes table, and find that there are three records with a Chord key of 14; these records have 3, 5, and 7 in the respective note key fields. You then go and find the three records in the Notes table with keys 3, 5, and 7, and you have your chord.

Putting that example into SQL (I'll just make up names for the fields in your tables; you can use whatever field names and table names you want in your design):

SELECT C.CHORDNAME, N.NOTENAME FROM NOTES N  
JOIN CHORDNOTES CN ON N.NOTEKEY = CN.NOTEKEY  
JOIN CHORDS C ON C.CHORDKEY = CN.CHORDKEY  
WHERE C.CHORDKEY = 14  
ORDER BY CN.NOTEORDER

The result would look like this:

CHORDNAME    NOTENAME  
-----------  -----------  
C Major      C  
C Major      E  
C Major      G  

That should give you a good base to start on. You'll need to work out how to get the key of the chord that the user selects from your UI (user interface), and how to display the result to the user. However, if you implement all this, you'll learn enough so that you'll have some idea of how to implement refinements that suggest themselves. Hope this helps, and good luck. :)

Answer 2

The fundamental idea of a chord is that there is a pitch, the root, and then a series of other pitches that have relationships to that root pitch, and implicitly, to each other.

I wouldn't make a big distinction between the natural notes, and those with alterations on them as you indicated in your bulletized list, but this could be argued either way.

I'd look at it as:

1) The user selects the pitch class for the root

2) User selects from {major, minor, sus2, sus4, no-third [*]}

• this is the characteristic of the "third"

3) The user selects from {P5, aug, dim, no-fifth? [*]}

• The default is the perfect fifth

4) The use selects from various extensions {7th, maj7, 13th, flat-9 ...}

• Here things start getting really complicated, and you'll have to decide what range of music you want to support.
• maybe you want a set of independent checks for this.

I only see two complications:

• Usually a diminisished chord impilies a minor third, but not always; sometimes, notably in jazz, you want a major third with a flattened fifth, so disentangling this case might be harder.

• Defining the range of chord "extensions" you'll need to have an idea of the scope of music that will be going into the data base, or a flexible system (vanilla strings maybe?) that allow for arbitrary extensions.

An option for handling the first case is

1) root pitch class

2) select from {major, minor, sus2, sus4, aug, dim, no-third [*]}

• major/minor/sus2/sus4 implies perfect 5th

• aug => major 3rd, dim => minor third

3) select extension

• as above but include the various flat-5/sharp-5 options here.

[*] depending on how esoteric your music gets you may need additional concepts.

Answer 3

The optimal schema for any database largely depends on the type of queries. Think about that the user may ask from such a database. Maybe which chords are the good potential candidates to follow it in a chord progression? Then you need to read some theory about chord progression rules, and support queries along these rules. Here may be some hints to start from.

Answer 4

From the OP comments:

I am building a web-app that will allow users to query the database in a friendly, easy-to-use way for a musician (targeting amateurs and hobbyists) who is not necessarily knowledgeable in music theory. An example would be: two beginner/intermediate guitarists jamming to a chord progression they're not familiar with and one wants to play lead over it, so he puts the chords into the app, the app queries the database and returns a few possible keys that they might be playing in, and then renders a guitar fretboard showing which notes on the fretboard are in that key

You need to define your goal more strictly before writing any more code.

The main point is weather your app can handle non diatonic chord progressions.

What will happen when someone inevitably puts in the 12 bar blues chords? (I7, IV7 and V7) Will your app be smart enough to recognize this extremely common non diatonic progression? and suggest the minor pentatonic, major pentatonic, mixolydian, blues scale, lydian dominant etc built from the roots of each chord as valid and common choices?

will it be smart enough to give the minor pent. as a valid choice for a dominant chord in this context? Will it know not to give this suggestion for 99% of other occurrences of dominant chords?

What will happen when other popular non diatonic chord progressions are input such as the Andalusian cadence? (http://en.wikipedia.org/wiki/List_of_popular_music_songs_featuring_Andalusian_cadences)

Will the app be smart enough to recognize that the phrygian dominant is an appropriate scale over the V chord?

What will happen when the user puts in "Hey Joe"?

These very common 'beginner' progressions will be the acid test. If your app can't deal with them then it won't be much use to anyone.

If you decide to avoid the complications of non diatonic progressions then it can be much simpler to achieve. In that case there's really no point having the input system allow, say a diminished chord as that is never a diatonic chord. But your whatkeyamiin.com site basically already does this..

If you go down this route I'd suggest simply having an image of the circle of fifths. the user selects chord after chord say, G, then C then Am then Em, as each additional chord is selected the possible diatonic chords reduces (non diatonic chords 'black' out) and the possible key become highlighted.

Answer 5

The first thing necessary to formulate your thesis is to understand your data and what other approaches have already been implemented. If you are thinking of a dynamic or interactive approach take a look at chords and scales identifier. The reference to "Lotus Music" was useful, but it presents a static interface.