SQL How to handle lots of comments / notes on a page

Question

I have an app that will generate lots of notes on a page. The user can click anywhere on a page to create a quick note (ex Acrobat Pro) These notes can be added and deleted with some line of javascript in a general <div id="notes">

<div id="notes">
    <div class="note" id="note-1"><input type="text" value="Some note"></div>
    <div class="note" id="note-2"><input type="text" value="Some note"></div>
    <div class="note" id="note-3"><input type="text" value="Some note"></div>
    <div class="note" id="note-4"><input type="text" value="Some note"></div>
    <div class="note" id="note-5"><input type="text" value="Some note"></div>
    <div class="note" id="note-6"><input type="text" value="Some note"></div>
    <div class="note" id="note-7"><input type="text" value="Some note"></div>
</div>

Would you say that it is a good idea to only store the markup of all the notes in the database instead of, in that case 7, 100 notes or more? That was my original idea; is calling 1 huge field is better for performance than 100 or more with shorter content (the value of the input)?

Answer 1

Having 1 huge field is just messy and doesn't fit well into the RDBMS model. It's more difficult just to look at the SQL data and make sense of it. You cannot index these types of fields, thus looking for a specific note takes very long. Insertions and deletions probably also take longer. You need specific functionality to handle splitting the data in this field.

Having 100 rows instead ... Well, that's a lot more rows (though not much at all if you look at medium-sized databases), but if you only store the minimum data, and index correctly, you can end up with a structure that's not much bigger or slower than a single field approach.

Basically, you'll need to think what you want to do with these notes. If the most common operation is just give me all notes, then you're probably fine with a single field. As soon as you start doing lots of insertions, deletions and searches, the many rows will outperform the single field. For maintainability it may be better to just go with the many rows in either case (in case there is some change in requirements).