Help with database design for archives inventory/database (Collections management) RRS feed

  • Question

  • (Previously posted on Microsoft Community - moderator suggested I post here instead)

    Disclaimer: Access Noob (Been tinkering with it for less than a month at work with help from articles and tutorials)

    Hi everyone,

    I'm an intern tasked to refine an inventory survey form for a repository. The original design has each field in its own table, like, FormatHousingMedia, Additional Media, Condition Assessment, Recommended Treatment, etc. Example


    ...Single sheet

    ...Bound material

    ...Unbound material



    ...Iron gall ink

    ...Ballpoint ink

    I would like to revise how we account for multivalued fields, like Media, Condition Assessment, Recommended Treatment. One record in the repository can have various media and various types of damages. The current form uses a checklist combo box for this, but this doesn't look like a good idea. When we export to excel for example, the values in the Condition Assessment field are separated by commas and there are very many different combinations of values.

    Do let me know if you have an idea how this database should be designed... I would appreciate it very much, thank you!

    P.S. New to Access

    For more information, the fields needed in the inventory form are

    Keyed In

    • Accession number (but it references the accession number in the main inventory that includes location, title, source, etc.)
    • Width
    • Depth
    • Date of survey (Date function)

    Chosen from a combo box, likely to be only a single value

    • Format

    Chosen from a combo box, likely to be multivalued

    • Surveyor
    • Primary support
    • Secondary support
    • Condition assessment
    • Recommended treatment
    • Housing
    • Housing condition assessment
    • Housing recommended treatment
    • Media
    • Additional media
    • Foreign object
    • Foreign object condition assessment
    • Foreign object recommended treatment

    Wednesday, June 20, 2018 7:14 AM

All replies

  • The first thing I'd recommend is that you do not use multi-valued fields.  They might seem a superficially attractive solution, but they do not fit comfortably in the database relational model and are very limited in what they can model.  A multi-valued field is really modelling a many-to-many relationship type between entity types.  However, the relationship type modelled can only be a binary relationship type, i.e. between two entity types, and cannot include any non-key attributes of the relationship type.  In reality, relationship types can be ternary, quaternary etc, and frequently a relationship type will have one or more non-key attributes.

    The conventional way to model a many-to-many relationship type, be it binary, ternary or whatever, is by means of a table which resolves the relationship type into two or more one-to-many relationship types.

    It sounds like the structure of your database has correctly modelled the referenced entity types, such as Formats and Media, as tables.  In database terms each document in the collection represents an instance of a relationship type between the referenced entity types.  However, this does not necessarily mean that the relationship type is simply a table in which there are foreign keys directly referencing each of the referenced tables.  It is very likely that there are relationship types between subsets of the referenced entity types, and the 'documents' table will need to reference the tables which model these relationship types between subsets of the entity types, rather than referencing each referenced entity type individually.

    You might like to take a look at in my public databases folder at:!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file use as its example a simple medical prescriptions database to illustrate how the necessary relationship types are built up across the database, centred in a PatientPrescriptions table which models the relationship type between patients and the drugs which they are prescribed.

    Ken Sheridan, Stafford, England

    Wednesday, June 20, 2018 4:58 PM