What are the best field data types for a table of musical keys
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.