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

Posted on 2006-05-03
Last Modified: 2012-08-14
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.
Question by:dzumwalt
    LVL 44

    Expert Comment

    by:Leigh Purvis
    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 ;-)
    LVL 13

    Expert Comment

    by:John Mc Hale
    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.

    LVL 44

    Expert Comment

    by:Leigh Purvis
    ^--- The unabridged version :-)
    LVL 77

    Accepted Solution

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

    LVL 44

    Expert Comment

    by:Leigh Purvis
    Ah yes - good point Pete - perhaps dzumwalt was unaware of that.
    I hadn't looked at it from that angle.

    Author Comment

    Thanks for the input. I didn't know that Pete. I appreciate the insight.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Well, I was right about something ;-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now