Best Field Data Types for a Table of Musical Keys

database-design

I'm fairly new to relational databases. I understand how fields may have int or text data structures, but when it goes beyond that, I feel a bit out of my element.

I have a table called: keys

It is supposed to hold information on which key a classical music composition was written in. An example entry could be: E minor.

What I did is create two columns for the key table: name which is the name of the key, taking on values ranging from A-G, as well as tonality which tells us whether the composition was major or minor.

It currently has this form:

enter image description here

Question: What is the best way to construct my keys table (in terms of efficiency/scalability) and what data structures should the fields have?

I think it would make sense to have the tonality field be a binary. But does binary in this case need to be 1's and 0's? Or is there an efficient way to include the whole string minor or major? I'm not sure if I need to create another table to relate it, or if it can be efficiently done with 1. Also is text an appropriate field type for key.name? It can only take on 7 values: A, B, C, D, E, F, G. (My data do not include sharps or flats).

Best Answer

This is a 'primarily opinion-based' question.

I would go for a table with the following fields:

  • key_id integer
  • key char(1)
  • tonality char(1)
  • name varchar2(50)

The first field is the primary key. The second and third field make up a unique key. For key there is A to G possible. For tonality I would use - for minor, + for major, and 'space' for 'normal'. For both those fields you can make a check constraint to prevent wrong values to enter.

Since there are not many rows in this table it be read completely into memory so it will not influence response times that much.

If you do not like the 'extra' primary key then you can also use:

  • key char(2)
  • name varchar2(50)

The key is primary key and holds A-, A, A+, etc.

Related Question