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

Pete
0
 
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 ;-)
0
 
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).

Example:

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
tbl_employment_history_comment
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


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


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

Regards.
0
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 :-)
0
 
Leigh PurvisDatabase DeveloperCommented:
Ah yes - good point Pete - perhaps dzumwalt was unaware of that.
I hadn't looked at it from that angle.
0
 
dzumwaltAuthor Commented:
Thanks for the input. I didn't know that Pete. I appreciate the insight.
0
 
Leigh PurvisDatabase DeveloperCommented:
Well, I was right about something ;-)
0
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.