11

I am building a web app that manipulates fairly complex data: guitar tabs.

    As a reference, guitar tabs look like this:
Eb|-------------------------------------------------------------------------|
Bb|-------------------------------------------------------------------------|
Gb|--5-5-5-5----------------------------------------------------------------|
Db|--5-5-5-5--3-3-3-3--7-7-7-7--5-5-5-5--2-2-2-2--3-3-3-3--2-2-2-2--5-5-5-5-|
Ab|--3-3-3-3--3-3-3-3--7-7-7-7--5-5-5-5--2-2-2-2--3-3-3-3--2-2-2-2--5-5-5-5-|
Eb|-----------1-1-1-1--5-5-5-5--3-3-3-3--0-0-0-0--1-1-1-1--0-0-0-0--3-3-3-3-|

Would it be more efficient for performance to store this data as a large chunk, or to break it up and store it on a "note by note" basis?

As a use case:
User changes first chord from:       to:
                         Eb|---   Eb|---
                         Bb|---   Bb|---
                         Gb|--5   Gb|--4
                         Db|--5   Db|--4
                         Ab|--3   Ab|--2
                         Eb|---   Eb|---

If I store it as a block, the code to manipulate the tabs would have to be much more complex. If I store it note by note, the database will have to be accessed a lot more. Which method is more efficient? Potentially, a lot of users will be modifying the data. I want the best performing web app. I will be using MySQL if that affects the answer at all.

Gabe Willard
  • 341
  • 1
  • 9
  • 2
    Better for what? Saving space? CPU power? IO? Something else? – Oded Apr 25 '12 at 16:11
  • Well, it's a web app. A lot of users are potentially going to be modifying data fairly frequently. I'd imagine a lot of factors like you mention affect it differently. I'm not so familiar with those specifics; that's partially why I'm asking here. – Gabe Willard Apr 25 '12 at 16:15
  • If you don't know what you are optimizing for, how can we answer? Thing is - build it first, if you have specific issues, then ask how to sort them out. – Oded Apr 25 '12 at 16:19
  • 12
    Do you not design databases before you build them? My question is on designing a database. Not troubleshooting one. I am not in the debugging stage yet, and even if I were, that would go to StackOverflow, not Programmers. Per the FAQ: Programmers covers algorithm and data structure concepts, design patterns, software architecture, software engineering... Not troubleshooting bottlenecks. – Gabe Willard Apr 25 '12 at 16:26
  • +1 very interesting problem and good job illustration a useful use case. Makes me wish I had a good excuse to develop a guitar tab app now. – Evan Plaice Apr 27 '12 at 10:07

3 Answers3

8

The number of operations is going to be the same either way. You do one query to get all the chords for a song, then you do one update every time a change is made. The difference is really in the size of the updates. With the block method, you have to save the entire song every time you change a chord. With the individual method, your updates will be smaller, and probably more efficient overall, though the difference may be negligible.

Another thing to consider is the note-by-note method is more normalized, meaning you'll have more query options open to you down the road if you use it. For example, beginners could filter out chords they don't know when searching for a song to learn, or you could allow searching based on the opening chords if someone doesn't know a song title. Even if you don't plan those features now, it will be a huge pain to change your database if you want something like that later.

Karl Bielefeldt
  • 147,435
5

Generally speaking, more normalization is good for several reasons:

  1. Less duplication of data, leading to a smaller physical database size.
  2. Better data integrity - you can use foreign keys to enforce certain requirements.
  3. Simpler update code, which you've identified.
  4. More indexable access routes to subsets of data.

Disadvantages (described well here) include:

  1. Normalization saves space, but space is cheap.
  2. Normalization simplifies updates, but reads are more common.
  3. Performance is generally better with less-normalized schemas.

I would suggest starting with a more normalized design, and only consider denormalizing if you run into performance problems.

Mike Partridge
  • 6,647
  • 1
  • 25
  • 39
  • With guitar tabs database, simplicity, consistency and integrity trumps performance. So I'd go with the simplest normalized schema I could come up with. – 9000 Apr 25 '12 at 17:59
2

Make your storage easiest to work with, and hard enough to screw up. Go with a reasonably normalized schema. Go with a schema that does not preclude usages other than you will need in your first release, if possible.

If all you need is to show tabs for a particular song, you could store lots of 6-tuples in a document-oriented DB (like MongoDB), fetching them as one document.

In an RDBMS, I'd store it similarly, in a table like this:

table tab_column (
  song_id integer not null foreign key references song(id),
  ordinal integer not null, -- position in the tabulature
  s1 number(2), -- position on 1st string
  ...
  s6 number(2),
  primary key(song_id, ordinal)
)

RDBMSes are good at simple queries like the one needed to show a song: 

select * from tab_column
where song_id = :song_id
order by ordinal;

Using limit and offset, you can show parts of a song.

Later it will be easy to link tab_column to a table that lists named chords, if you can recognize a chord.

This is probably the simplest schema possible; I'd start with it.

9000
  • 24,232