Skip to main content
Humanities LibreTexts

9.1: Appendix A - Database Rules and Datatypes

  1. Last updated
  2. Save as PDF
  • Page ID
    174510

    • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    Text / short text

    This is the default datatype for your fields which will be assigned by the database whenever you add a new field to a table

    This datatype will allow the entry of textual and numerical values, although it will treat that latter differently from numerical values entered into a ‘number’ datatype field

    Fields with this datatype will generally allow a maximum of 255 characters to be entered

    Memo / long text

    Fields with this datatype are used for lengthy texts and combinations of text and numbers

    Up to 65,000 characters can be entered (the precise number may change depending on the database software being used)

    Data in these types of field cannot be sorted

    Data in these types of field are difficult, although not impossible, to query.

    Number

    This datatype allows the entry of numerical data that can be used in arithmetical calculations

    There are several variations of this datatype, which control aspects of the numbers that can be entered, such as the size of the numbers, the number of decimal places and so on:

    Byte: Stores numbers from 0 to 255 (no decimals)

    Decimal: stores positive and negative numbers down to 28 decimal places

    Integer: Stores numbers from –32,768 to 32,767 (no decimals)

    Long integer: (default) stores numbers from –2,147,483,648 to 2,147,483,647 (no decimals)

    Single: stores very large positive and negative numbers to 7 decimal places

    Double: stores very large positive and negative numbers to 15 decimal places

    Date/time

    This datatype enables the entry of dates covering the period 100 through to 9999

    This datatype can be customized in order to control the format of dates that are entered

    Warning: in Microsoft Access, the Office autocorrect feature may well change some of your dates if it is active (e.g. “02/12/04” will autocorrect to “02/12/2004” unless you enter the year in full)

    This datatype will allow the sorting of records chronologically, which the same values entered into a text datatype field would not (the latter would sort the records alphabetically – alphabetically “01/01/2010” would come before “31/12/1245”)

    Currency

    This datatype allows the entry of numerical values data used in mathematical calculations involving data with one to four decimal places, with the inclusion of currency symbols

    Aut\nonumber

    This datatype automatically generates a unique sequential or random value whenever a new record is added to a table.

    AutoNumber fields cannot be updated, that is, you cannot enter data into them manually

    Yes/no / Binary / Boolean

    A field with this type of datatype will only contain one of two values (Yes/No, True/False, or On/Off)

    Quite often database software will represent this type of field as a checkbox in the table

    The underlying text value of a yes/no field will be -1 (for yes) or 0 (for no)

    OLE

    A field with this datatype is one in which another file is embedded, as a Microsoft Excel spreadsheet, a Microsoft Word document, an image, a sound or video file, an html link, or indeed any other type of file

    Every field in every table will need to have one of these datatypes assigned, and the decision as to which type is chosen should be factored into the database design process. For most fields the datatype chosen will be either ‘text’ or ‘number’. Keep in mind how these two datatypes treat numerical data differently, particularly in terms of how they cause data to be sorted:

    • 1,10,11,2,20,21,3,4,5,6,7,8,9 is how data will be sorted if the datatype is ‘text’ (that is, alphabetically)
    • 1,2,3,4,5,6,7,8,9,10,11,20,21 is how data will be sorted if the datatype is ‘number’ (that is, numerically)

    This page titled 9.1: Appendix A - Database Rules and Datatypes is shared under a CC BY-NC-SA 4.0 license and was authored, remixed, and/or curated by Stephanie Cole, Kimberly Breuer, Scott W. Palmer, and Brandon Blakeslee (Mavs Open Press) via source content that was edited to the style and standards of the LibreTexts platform; a detailed edit history is available upon request.