2

I have a table that represents a "note". This note has things like a title and various other meta data. However, I'm trying to figure out the best practice for storing the contents of the note.

Currently, my strategy is to have the contents of the note be a VARCHAR(10000) and just store a NULL value if there are no contents. But, I'm not sure if that's the right thing to do, or if this will cause performance or storage issues.

I am very new to constructing databases - am I going about this the right way?

2 Answers 2

1

A note with no contents sounds like a fairly meaningless object to me. I would model this by the absence of such tuple in the notes table!

p.s. a note's title is an attribute. An example of metadata ("data about data") would be the date a note's title attribute was most recently updated.

2
  • Thinking of email. gmail seems to automatically save a draft of an email with a title and/or recipients but without a body but I'd rather it didn't! For me, the only useful email with an empty body should is either a) it is only the attachment that is important, or b) it is only the title that is important. In both cases I will add metadata to the title to indicate the email type: # prefix to mean 'body deliberately left empty' and (attached) to indicate an attachment.
    – onedaywhen
    Mar 2, 2012 at 12:44
  • ...So I would prefer to have message types that imply no body rather than having to use an all purpose email type and have to explicitly leave the body blank then explicitly indicate that I have done so on purpose!
    – onedaywhen
    Mar 2, 2012 at 12:47
1

Depending on your database of choice VARCHAR(10000) may not be the best way to store your data. You may take up a large amount of disk space depending on how your database implements the storage of VARCHAR. Some databases also do terrible with indexes over large VARCHAR's.

What we've done at our company is we set up an ID for a message or text and then a counter (starting with 1 for each NEW message or text). We only use CHAR or VARCHAR to a max of a certain amount (like say 50 or 100). We could use 255. Use your own judgement.

Parse your text and break it into numbered chunks. Now you can search based on the ID. If you want to search over the text it is more manageable, and you can always rebuild the text using the ID and the counters to know which ones relate and in which order.

Just a thought (from my past life as an application developer).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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