When should I use a memo field vs. a large text field.

I need to keep some information that will be a maximum of 75 characters in length. The thing is, only a small subset of the actual records in the table will actually contain data in this field.

My question is, what is the cost-to-the-database-filesize threshold to determine whether this new field should be a straight text(75) field versus a memo field?

Thanks for your help.
Who is Participating?
peter57rConnect With a Mentor Commented:
Hi dzumwalt,
>  maximum of 75 characters
so why are you considering a memo field at all?

(Access text fields only occupy the space they need to store the data they hold.)

Leigh PurvisDatabase DeveloperCommented:
The only difference that I imagine you're envisaging is that Memo fields aren't stored internally with the rest of the table?
But a Text field is appropriate for what you're asking - but it needn't be stored in the same table.

If you continue with your process of normalization - then if the field isn't always required then it could be separated out into a table of it's own.
If you can be bothered ;-)
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectCommented:
I'm not sure that you're asking the right question!

If you are following the relational design model, and you wish to keep redundancy/duplication to a minimum, then the correct way to approach this would be to create a separate table for this additional information, giving it a primary key (such as the key field of the entity type to which the information relates, if this is a one-one relationship type, or a composite primary key for a many-many relationship).


Suppose the information you wish to store relates to a person's employment history comments, then you might have:

For a one-one relationship between employees and comments
EmployeeID (Primary Key from employee table)
Comment (The actual info)

For a many-many relationship (i.e. none or more employees can have none or more comments associated with them

EmployeeID (Primary Key from employee table)
CommentID (Primary Key from employment history comments)

CommentID (Autonumber, Primary Key)
Comment (Actual info)

Then by creating the correct joins you will have a correctly normalized database, and the question you posed should now be a non-issue, but personally, I would use a Text field for text entries <= 255 characters.

Hope this makes sense.

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Leigh PurvisDatabase DeveloperCommented:
^--- The unabridged version :-)
Leigh PurvisDatabase DeveloperCommented:
Ah yes - good point Pete - perhaps dzumwalt was unaware of that.
I hadn't looked at it from that angle.
dzumwaltAuthor Commented:
Thanks for the input. I didn't know that Pete. I appreciate the insight.
Leigh PurvisDatabase DeveloperCommented:
Well, I was right about something ;-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.