Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

2 Answers

up vote 1 down vote

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.

share|improve this answer
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 '12 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 '12 at 12:47
up vote 1 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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