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).

  • If I understand well then you can have 21 different entries in your table (A-minor, A, and A-major till G-minor, G, G-major)? – Marco May 8 '18 at 8:55
  • @Marco yes, that's how I initially envisioned it, having every combination of key + tonality. However, maybe it would make more sense to create a new field or table for tonality. I'm uploading my latest schema for reference. – Arash Howaida May 8 '18 at 9:27
  • Random question: may I ask what tool you used for the visualization of your schema? – PicoDeGallo May 8 '18 at 18:14

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.

  • Eventually I would like to call JOIN on the key table and provide a user friendly way to see the key in full string from, I guess the first approach you suggested would work best for that? – Arash Howaida May 8 '18 at 9:42
  • Not really. Just a matter of what you want. With the first option you can change the value of key and tonality without 'breaking' the link. For the second one you cannot. Here the 'advantage' is that you can see the value in the rows of your composition table without checking the keys table. But since the number of rows and size of the rows is very low (21 rows less the 1K) the table will end up in memory (and probably stay there) when you use it so access is very fast. – Marco May 8 '18 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.