[[Database schema information]]
 
Trace: » Database schema information
Table of Contents

Database schema information

Here you will find information about the databases, tables & columns (fields) used by ReadingRecord.

Config database

The config database contains ReadingRecord configuration settings for a specific site (library):

  • Global settings, which affect the site (library) as a whole.
  • Staff & volunteer logins.
  • Per-program settings.

The config database has 3 tables:

  • global
  • login
  • program

ReadingRecord uses one config database per site (library). In general, the config database is named using part or all of the hostname portion of the URL. For example, if the URL is 'wpl.readingrecord.net', the config database would be 'wpl_config'.

global table

The global table contains configuration settings that affect the site (library) as a whole. For example, the library name, banner image, etc. The global table contains the following columns:

  • globalId
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: 1
    • Description: Currently there is only 1 record in this table, so globalId should always be 1.
  • siteHost
    • Format: Text.
    • Example: This site hosted by Limited North, LLC.
    • Default: This site hosted by message.
    • Description: This column is reserved for future use.
  • indexStart
    • Format: yyyy-mm-dd
    • Example: 2013-01-07
    • Default: 2013-01-01
    • Description: This field is the start date for displaying the main index page for patron self-registration. Since individual programs can have different self-registration start dates, this field should be set the same as the earliest self-registration start date (config.program.regStart). When before this date, patrons will see the “registrationFailure” page. Note: This field does NOT affect access to the staff module.
  • indexEnd
    • Format: yyyy-mm-dd
    • Example: 2013-08-06
    • Default: 2020-12-31
    • Description: This field is the end date for displaying the main index page for patron self-registration. Since individual programs can have different self-registration end dates, this field should be set the same as the last self-registration end date (config.program.regEnd). When after this date, patrons will see the “registrationFailure” page. Note: This field does NOT affect access to the staff module.
  • pageTitle
    • Format: Text
    • Example: Westminster Public Library - ReadingRecord
    • Default: Template Library - ReadingRecord
    • Description: This text appears in the browser title bar.
  • libraryName
    • Format: Text
    • Example: Westminster Public Library
    • Default: Template Library
    • Description: The library name. This text appears in a variety of places within ReadingRecord.
  • imageDir
    • Format: Text
    • Example: wpl.readingrecord.org
    • Default: template.readingrecord.net
    • Description: The directory containing the library banner/logo used at the top of the patron self-registration pages. It should match the DNS name of the site.
  • bodyColor
    • Format: Text
    • Example: bgcolor=”#D8D8BF”
    • Default: bgcolor=”#D8D8BF”
    • Description: HTML bgcolor element for setting the page background color.
  • hrOpts
    • Format: Text
    • Example: width=“90%” color=”#ffbb00”
    • Default: width=“90%” color=”#ffbb00”
    • Description: HTML elements for setting horizontal rule width & color options.
  • allowDups
    • Format: 0 (disallow) or 1 (allow).
    • Example: 1
    • Default: 1
    • Description: Allow or disallow patrons to register for more than one program. If only one program is enabled, this option has no effect.
  • enable1
    • Format: 0 (disabled) or 1 (enabled).
    • Example: 0
    • Default: 1
    • Description: Disable or enable program #1. If enabled, config.global.db1 must also be set.
  • enable2
    • Format: 0 (disabled) or 1 (enabled).
    • Example: 0
    • Default: 0
    • Description: Disable or enable program #2. If enabled, config.global.db2 must also be set.
  • enable3
    • Format: 0 (disabled) or 1 (enabled).
    • Example: 0
    • Default: 0
    • Description: Disable or enable program #3. If enabled, config.global.db3 must also be set.
  • enable4
    • Format: 0 (disabled) or 1 (enabled).
    • Example: 0
    • Default: 0
    • Description: Disable or enable program #4. If enabled, config.global.db4 must also be set.
  • enable5
    • Format: 0 (disabled) or 1 (enabled).
    • Example: 0
    • Default: 0
    • Description: Disable or enable program #5. If enabled, config.global.db5 must also be set.
  • enable6
    • Format: 0 (disabled) or 1 (enabled).
    • Example: 0
    • Default: 0
    • Description: Disable or enable program #6. If enabled, config.global.db6 must also be set.
  • db1
    • Format: Text
    • Example: wpl_1
    • Default: template_1
    • Description: Name of the database for program #1.
  • db2
    • Format: Text
    • Example: wpl_2
    • Default: none
    • Description: Name of the database for program #2.
  • db3
    • Format: Text
    • Example: wpl_3
    • Default: none
    • Description: Name of the database for program #3.
  • db4
    • Format: Text
    • Example: wpl_4
    • Default: none
    • Description: Name of the database for program #4.
  • db5
    • Format: Text
    • Example: wpl_5
    • Default: none
    • Description: Name of the database for program #5.
  • db6
    • Format: Text
    • Example: wpl_6
    • Default: none
    • Description: Name of the database for program #6.

login table

The login table contains login, password & report access information for staff & volunteers. It has 5 columns:

  • login_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: None (automatically assigned).
    • Description: A unique number assigned to each staff/volunteer account.
  • user_name
    • Format: Text. Letters only, no numbers, spaces or special characters.
    • Example: staff
    • Default: none
    • Description: Login user name for staff or volunteer.
  • password
    • Format: Text.
    • Example: PassW0rd
    • Default: none
    • Description: Password for user account.
  • real_name
    • Format: Text.
    • Example: Staff
    • Default: none
    • Description: User's real name.
  • reports_access
    • Format: 0 (Deny) or 1 (Allow).
    • Example: 0
    • Default: none
    • Description: Deny or allow access to ReadingRecord reports.

program table

The program table contains configuration options for individual programs. It contains the following columns:

  • progId
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: ReadingRecord supports up to 6 programs, so there will be 1 row (record) for each program. Records for unused program(s) can be safely ignored.
      • The value of 'progId' corresponds with 'enableN' & 'dbN' in the global table. For example:
        • progId=1 corresponds with enable1 & db1.
        • progId=2 corresponds with enable2 & db2.
        • …and so on up to 6.
  • regStart
    • Format: yyyy-mm-dd
    • Example: 2013-01-07
    • Default: 2013-01-01
    • Description: The date patron self-registration begins. When before this date, information & the self-registration link will not appear on the self-registration index page. Note: Programs can have different self-registration start dates.
  • regEnd
    • Format: yyyy-mm-dd
    • Example: 2013-08-06
    • Default: 2020-12-31
    • Description: The date patron self-registration ends. When after this date, information & the self-registration link will not appear on the self-registration index page. Note: Programs can have different self-registration end dates.
  • altURL
    • Format: Text in the form of a URL.
    • Example: http://www.google.com
    • Default: none
    • Description: This field allows you to publish information about a reading program NOT using ReadingRecord, as well as provide a URL for more information. When text appears in this field, program information will appear along with the alternate URL. No link to register via ReadingRecord will be available. You might use this option if you have an adult reading program that has a separate website rather than ReadingRecord. (Note: It is Jean Ward's fault that this field and corresponding code exist! ;-))
  • longPgrmName
    • Format: Text
    • Example: Get with the program - 2013 Summer Reading for kids.
    • Default: Long Program Name
    • Description: Long(ish) program name displayed on the self-registration pages.
  • shortPgrmName
    • Format: Text
    • Example: Kids
    • Default: Short Program Name
    • Description: Short program name displayed in various places on the staff pages.
  • pgrmRange
    • Format: Text
    • Example: Ages 1-4
    • Default: Program Range
    • Description: Generally used to indicate program ages, appears as part of “Click here to register for [longPgrmName] (pgrmRange)” link on the self-registration index page.
  • pgrmInfo
    • Format: Text
    • Example: The Children's Summer reading program for Library XYZ runs from blah, blah, something, something, ham sandwich…
    • Default: none
    • Description: Informational paragraph about the reading program. Can include some basic HTML elements like paragraph tags, font size/color/style and an embedded link to elsewhere. Appears on the self-registration index page.
  • useLibrary
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track the patron's home library. If enabled, the corresponding program's 'library' table must be populated with a list of branches.
  • libraryLabel
    • Format: Text
    • Example: Library You Visit Most Often:
    • Default: Home Library:
    • Description: Label for the “library” drop-down.
  • usePersonalHeader
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: If enabled, displays the “Personal Info” header. In most cases this should be enabled.
  • personalHeaderLabel
    • Format: Text
    • Example: Personal Info:
    • Default: Reader Info:
    • Description: Label for the “Personal Info” header.
  • firstNameLabel
    • Format: Text
    • Example: Your First Name:
    • Default: First Name:
    • Description: Label for the “First Name” field.
  • lastNameLabel
    • Format: Text
    • Example: Your Last Name:
    • Default: Last Name:
    • Description: Label for the “Last Name” field.
  • phoneDigits
    • Format: 7 or 10
    • Example: 10
    • Default: 10
    • Description: Configures ReadingRecord for 7 (no area code) or 10 (area code) digit phone numbers.
  • phoneError
    • Format: Text
    • Example: Please use the format 123-456-7890. Don't want to give your phone number? Enter 000-000-0000.
    • Default: Please use the format 123-456-7890. No phone? Enter 000-000-0000.
    • Description: Error message displayed when the phone number is entered incorrectly.
  • phoneAreaCode
    • Format: nnn-
    • Example: 303-
    • Default: none
    • Description: Used to pre-populate the phone number field with an area code.
  • useAge
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track the patron's age. If enabled, the corresponding program's 'age' table must be populated with a list of ages.
  • ageLabel
    • Format: Text
    • Example: Your Age:
    • Default: Age:
    • Description: Label for the “age” field.
  • useBdate
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track the patron's birth date. Currently this is a plain text box, no date validation is available for 2013.
  • requireBdate
    • Format: 0 (optional) or 1 (required)
    • Example: 1
    • Default: 1
    • Description: When enabled, is the “birth date” field optional or required?
  • bdateLabel
    • Format: Text
    • Example: DOB:
    • Default: Birth Date:
    • Description: Label for the “birth date” field.
  • useGender
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track the patron's gender. If enabled, the corresponding program's 'gender' table must be populated with a list of genders.
  • genderLabel
    • Format: Text
    • Example: Boy or Girl?
    • Default: Gender:
    • Description: Label for the “gender” field.
  • useCity
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track the patron's city. If enabled, the corresponding program's 'city' table must be populated with a list of cities.
  • cityLabel
    • Format: Text
    • Example: City:
    • Default: City:
    • Description: Label for the “city” field.
  • useZipcode
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track the patron's zipcode. If enabled, the corresponding program's 'zipcode' table must be populated with a list of zipcodes.
  • zipcodeLabel
    • Format: Text
    • Example: Zipcode:
    • Default: Zipcode:
    • Description: Label for the “zipcode” field.
  • useEmail
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track the patron's e-mail address.
  • requireEmail
    • Format: 0 (optional) or 1 (required)
    • Example: 1
    • Default: 1
    • Description: When tracking the patron's e-mail address, is it optional or required?
  • emailLabel
    • Format: Text
    • Example: E-mail:
    • Default: E-mail Address:
    • Description: Label for the “email” field.
  • useBarcode
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track the patron's library barcode.
  • requireBarcode
    • Format: 0 (optional) or 1 (required)
    • Example: 1
    • Default: 1
    • Description: When tracking the patron's library barcode, is it optional or required?
  • barcodeLabel
    • Format: Text
    • Example: Barcode:
    • Default: Library Barcode:
    • Description: Label for the “barcode” field.
  • useSchoolHeader
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: If enabled, displays the “School Info” header. Should be enabled when tracking grade and/or school.
  • schoolHeaderLabel
    • Format: Text
    • Example: Grade & School:
    • Default: School Info:
    • Description: Label for the “school info” header.
  • useGrade
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track the patron's grade. If enabled, the corresponding program's 'grade' table must be populated with a list of grades.
  • gradeLabel
    • Format: Text
    • Example: Grade entering in Fall:
    • Default: Grade:
    • Description: Label for the 'grade' field.
  • useSchool
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track the patron's school. If enabled, the corresponding program's 'school' table must be populated with a list of schools.
  • schoolLabel
    • Format: Text
    • Example: School attending in Fall:
    • Default: School:
    • Description: Label for the 'school' field.
  • useGuardianHeader
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: When tracking guardian info, this field is normally enabled.
  • guardianHeaderLabel
    • Format: Text
    • Example: Parental Info:
    • Default: Guardian Info:
    • Description: Label for the “guardian info” header.
  • useGuardianFirstName
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track guardian first name.
  • requireGuardianFirstName
    • Format: 0 (optional) or 1 (required)
    • Example: 1
    • Default: 1
    • Description: When tracking the guardian's first name, is it optional or required?
  • guardianFirstNameLabel
    • Format: Text
    • Example: Parent's First Name:
    • Default: Guardian First Name:
    • Description: Label for the “guardian first name” field.
  • useGuardianLastName
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track guardian last name.
  • requireGuardianLastName
    • Format: 0 (optional) or 1 (required)
    • Example: 1
    • Default: 1
    • Description: When tracking the guardian's last name, is it optional or required?
  • guardianLastNameLabel
    • Format: Text
    • Example: Parent's Last Name:
    • Default: Guardian Last Name:
    • Description: Label for the “guardian last name” field.
  • useGuardianPhone
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track guardian's phone.
  • requireGuardianPhone
    • Format: 0 (optional) or 1 (required)
    • Example: 1
    • Default: 1
    • Description: When tracking the guardian's phone, is it optional or required?
  • guardianPhoneLabel
    • Format: Text
    • Example: Parent's Phone
    • Default: Guardian Phone
    • Description: Label for the “guardian phone” field.
  • useGuardianEmail
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track guardian's e-mail address.
  • requireGuardianEmail
    • Format: 0 (optional) or 1 (required)
    • Example: 1
    • Default: 1
    • Description: When tracking the guardian's e-mail address, is it optional or required?
  • guardianEmailLabel
    • Format: Text
    • Example: Parent's E-mail:
    • Default: Guardian E-mail Address:
    • Description: Label for the “guardian e-mail” field.
  • useGuardianBarcode
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track guardian's barcode.
  • requireGuardianBarcode
    • Format: 0 (optional) or 1 (required)
    • Example: 1
    • Default: 1
    • Description: When tracking the guardian's barcode, is it optional or required?
  • guardianBarcodeLabel
    • Format: Text
    • Example: Parent's Barcode:
    • Default: Guardian Barcode:
    • Description: Label for the “guardian barcode” field.
  • useConsent
    • Format: 0 (no) or 1 (yes)
    • Example: 1
    • Default: 1
    • Description: Track patron consent to collect information.
  • requireConsent
    • Format: 0 (optional) or 1 (required)
    • Example: 1
    • Default: 1
    • Description: When tracking patron consent, is it optional or required?
  • consentLabel
    • Format: Text
    • Example: Give consent:
    • Default: Consent:
    • Description: Label for the “consent” field. (To the left of the checkbox.)
  • consentInfo
    • Format: Text
    • Example: I give the library permission to collect this information for summer reading purposes.
    • Default: I give the library permission to collect this information.
    • Description: Info fof the “consent” field. (To the right of the checkbox.)
  • consentError
    • Format: Text
    • Example: Please give your consent.
    • Default: You must give your consent to register.
    • Description: Error message displayed when the “consent” checkbox is left unchecked.
  • regSuccessMssg
    • Format: Text
    • Example: Thank you for registering. Be sure to stop by the Children's desk to pickup your reading log.
    • Default: none
    • Description: Message displayed when a patron successfully self-registers. Can include some basic HTML elements like paragraph tags, font size/color/style and an embedded link to elsewhere (like a URL to print their own reading log, more information about the reading program, etc.). Appears on the “registrationSuccessful” page.

Program database

The program database contains ReadingRecord configuration settings & data for a specific program:

  • Values used to populate drop-down lists, including: age, city, grade, level, library, school & zipcode.
  • Data for registered readers.
  • Data for completed levels.

The program database has 10 tables:

  • age
  • city
  • gender
  • grade
  • level
  • library
  • reader
  • record
  • school
  • zipcode

ReadingRecord uses 1 program database per configured program (maximum 6). In general, the program database(s) are named using part or all of the hostname portion of the URL, same as the config DB. For example, if the URL is wpl.readingrecord.net, the program DB for program 1 would be 'wpl_1', the program DB for program 2 would be 'wpl_2', and so on.

age table

The age table is used to create the age drop-down list. It must be populated when tracking by age (config.program.useAge=1).

  • age_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: System generated number. NOTE: Do not change this number as it is referenced by other tables.
  • desc
    • Format: Text
    • Example: 18
    • Default: none
    • Description: Ages can be specific ages (1, 2, 3), ranges (0-3, 4-6, 7-11), descriptions (Child, Teen, Adult) or just about anything else you can think of.
  • sort_ord
    • Format: Integer between 0-99999, must be unique within the age table.
    • Example: 10
    • Default: none
    • Description: Controls sort order of items in the age drop-down list. Note: It is suggested that values be incremented by 10. This makes it easy to change the sort order and/or add additional ages if necessary.

city table

The city table is used to create the city drop-down list. It must be populated when tracking by city (config.program.useCity=1).

  • city_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: System generated number. NOTE: Do not change this number as it is referenced by other tables.
  • desc
    • Format: Text
    • Example: Westminster
    • Default: none
    • Description: While cities are generally a specific city (Westminster), you could append a zipcode (Westminster, 80031) instead of using the zipcode field.
  • sort_ord
    • Format: Integer between 0-99999, must be unique within the city table.
    • Example: 10
    • Default: none
    • Description: Controls sort order of items in the city drop-down list. Note: It is suggested that values be incremented by 10. This makes it easy to change the sort order and/or add additional cities if necessary.

gender table

The gender table is used to create the gender drop-down list. It must be populated when tracking by gender (config.program.useGender=1).

  • gender_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: System generated number. NOTE: Do not change this number as it is referenced by other tables.
  • desc
    • Format: Text
    • Example: Male
    • Default: none
    • Description: You can use descriptions like Boy/Girl, Male/Female & even include “prefer not to give” if desired.
  • sort_ord
    • Format: Integer between 0-99999, must be unique within the gender table.
    • Example: 10
    • Default: none
    • Description: Controls sort order of items in the gender drop-down list. Note: It is suggested that values be incremented by 10. This makes it easy to change the sort order and/or add additional genders if necessary.

grade table

The grade table is used to create the grade drop-down list. It must be populated when tracking by grade (config.program.useGrade=1).

  • grade_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: System generated number. NOTE: Do not change this number as it is referenced by other tables.
  • desc
    • Format: Text
    • Example: 12th
    • Default: none
    • Description: Grades can be specific grades (Preschool, Kindergarten, 1st, 2nd, 3rd), ranges (Elementary, Middle School, High School), descriptions (Completed High School, Some College, Completed College,) or just about anything else you can think of. You may also want to include “NOT IN SCHOOL”, “HOMESCHOOLED” and other special cases.
  • sort_ord
    • Format: Integer between 0-99999, must be unique within the grade table.
    • Example: 10
    • Default: none
    • Description: Controls sort order of items in the grade drop-down list. Note: It is suggested that values be incremented by 10. This makes it easy to change the sort order and/or add additional grades if necessary.

level table

The level table is used to populate the completed levels checkboxes & prize instructions. While levels are not strictly required, you will need at least one to generate level statistics and to use the random drawing feature.

  • level_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: System generated number. NOTE: Do not change this number as it is referenced by other tables.
  • desc
    • Format: Text
    • Example: Level 1
    • Default: none
    • Description: Levels can be just about anything you can think of. Note: Level descriptions should be unique in order to prevent ambiguity on reports & CSV data. Some examples include:
      • Level 1, Level 2, Level 3, Bonus Level 1, Bonus Level 2
      • Week 1, Week 2, Week 3, Week 4
      • Task 1, Task 2, Task 3, Task 4
      • Program registration, Halfway there, Program completion
      • Mile 1, Mile 2, Mile 3, Mile 4
      • Read 4 hours (1), Read 4 hours (2), Read 4 hours (3)
      • Read 4 hours, Read 4 additional hours, Read bonus 4 hours
      • Book 1, Book 2, Book 3, Book 4
      • Pages 0-99, Pages 100-199, Pages 200-299
      • Mystery Book, Sci-Fi Book, Nonfiction Book
      • Read a book, Listen to an audiobook, download an e-book
  • info
    • Format: Text
    • Example: Give 1st level prize & put name on board
    • Default: none
    • Description: Brief instructions to staff about what prize to give / what to do next.
  • sort_ord
    • Format: Integer between 0-99999, must be unique within the level table.
    • Example: 10
    • Default: none
    • Description: Controls sort order of items on the “track completed levels” page. Note: It is suggested that values be incremented by 10. This makes it easy to change the sort order and/or add additional levels if necessary.

library table

The library table is used to create the home library drop-down list. It must be populated when tracking by library (config.program.useLibrary=1).

  • library_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: System generated number. NOTE: Do not change this number as it is referenced by other tables.
  • desc
    • Format: Text
    • Example: Main Branch
    • Default: none
    • Description: Libraries are generally a list of your branches. Be sure to include bookmobile, homebound and any other “locations” you want to track.
  • sort_ord
    • Format: Integer between 0-99999, must be unique within the library table.
    • Example: 10
    • Default: none
    • Description: Controls sort order of items in the library drop-down list. Note: It is suggested that values be incremented by 10. This makes it easy to change the sort order and/or add additional libraries if necessary.

reader table

The reader table contains data about registered readers and is updated by ReadingRecord. It contains the following columns (fields):

  • reader_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: System generated number. NOTE: Do not change this number as it is referenced by other tables.
  • date_added
    • Format: yyyy-mm-dd hh:mm:ss
    • Example: 2013-01-07 22:15:05
    • Default: none
    • Description: Date the registration was added.
  • date_updated
    • Format: yyyy-mm-dd hh:mm:ss
    • Example: 2013-08-06 09:31:58
    • Default: none
    • Description: Date the registration was updated by staff.
  • reg_ip
    • Format: IPv4 address in standard dotted notation.
    • Example: 192.168.1.44
    • Default: none
    • Description: The IP address of the computer where the registration was entered.
  • reg_host
    • Format: Text
    • Example: wpl.readingrecord.org
    • Default: none
    • Description: FQDN of the URL used to register, should always match the site's hostname. Used primarily for troubleshooting.
  • library_id
    • Format: Numeric
    • Example: 1
    • Default: none
    • Description: Used to translate program.library.library_id into corresponding description (program.library.desc).
  • first_name
    • Format: Text
    • Example: Bob
    • Default: none
    • Description: The reader's first name.
  • last_name
    • Format: Text
    • Example: Loblaw
    • Default: none
    • Description: The reader's last name.
  • phone
    • Format: nnn-nnn-nnnn or nnn-nnnn
    • Example: 123-456-7890 or 123-4567
    • Default: none
    • Description: Phone number in 10 or 7 digit format, including spaces.
  • age_id
    • Format: Numeric
    • Example: 1
    • Default: none
    • Description: Used to translate program.age.age_id into corresponding description (program.age.desc).
  • bdate
    • Format: Text
    • Example: 01/1969
    • Default: none
    • Description: The reader's birth date.
  • gender_id
    • Format: Numeric
    • Example: 1
    • Default: none
    • Description: Used to translate program.gender.gender_id into corresponding description (program.gender.desc).
  • city_id
    • Format: Numeric
    • Example: 1
    • Default: none
    • Description: Used to translate program.city.city_id into corresponding description (program.city.desc).
  • zipcode_id
    • Format: Numeric
    • Example: 1
    • Default: none
    • Description Used to translate program.zipcode.zipcode_id into corresponding description (program.zipcode.zipcode.desc).
  • email
    • Format: E-mail address
    • Example: bob@loblaw.com
    • Default: none
    • Description: The reader's e-mail address.
  • barcode
    • Format: Text
    • Example: 23020800360993
    • Default: none
    • Description: The reader's library barcode.
  • grade_id
    • Format: Numeric
    • Example: 1
    • Default: none
    • Description: Used to translate program.grade.grade_id into corresponding description (program.grade.desc).
  • school_id
    • Format: Numeric
    • Example: 1
    • Default: none
    • Description: Used to translate program.school.school_id into corresponding description (program.school.desc).
  • date_validated
    • Format: yyyy-mm-dd hh:mm:ss
    • Example: 2013-01-18 13:42:29
    • Default: 0000-00-00 00:00:00
    • Description: Date the reader's registration was marked “valid” by staff, either by checking the “date validated” box on the registration screen or by checking level(s) as complete.
  • notes
    • Format: Text
    • Example: This patron has already been given their level 2 prize, please collect their reading log next time they come to the desk.
    • Default: none
    • Description: Notes entered by staff (not visible to patron).
  • guardian_first_name
    • Format: Text
    • Example: Frank
    • Default: none
    • Description: The guardian's first name.
  • guardian_last_name
    • Format: Text
    • Example: Loblaw
    • Default: none
    • Description: The guardian's last name.
  • guardian_phone
    • Format: nnn-nnn-nnnn or nnn-nnnn
    • Example: 123-456-7890 or 123-4567
    • Default: none
    • Description: Guardian phone number in 10 or 7 digit format, including spaces.
  • guardian_email
    • Format: E-mail address
    • Example: frank@loblaw.com
    • Default: none
    • Description: The guardian's e-mail address.
  • guardian_barcode
    • Format: Text
    • Example: 23020800360993
    • Default: none
    • Description: The guardian's library barcode.
  • consent
    • Format: Numeric
    • Example: 1
    • Default: none
    • Description: 0 (consent not given) or 1 (consent give)

record table

The record table contains data about completed levels (reading records, if you will ;-)) and is updated by ReadingRecord. It contains the following columns (fields):

  • record_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: System generated number. NOTE: Do not change this number as it is referenced by other tables.
  • reader_id
    • Format: Numeric
    • Example: 1
    • Default: none
    • Description: References the corresponding record in the program.reader table.
  • level_id
    • Format: Numeric
    • Example: 1
    • Default: none
    • Description: Used to translate program.level.level_id into corresponding description (program.level.desc).
  • date_completed
    • Format: yyyy-mm-dd hh:mm:ss
    • Example: 2013-01-29 14:27:04
    • Default: none
    • Description Date the level was marked as complete.

school table

The school table is used to create the school drop-down list. It must be populated when tracking by school (config.program.useSchool=1).

  • school_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: System generated number. NOTE: Do not change this number as it is referenced by other tables.
  • desc
    • Format: Text
    • Example: Westminster HS
    • Default: none
    • Description: Schools can be specific schools (Gregory Hills MS, Westminster HS, Vocational Tech), districts (District 12, District 50) or just about anything else you can think of. You may also want to include “NOT IN SCHOOL”, “HOMESCHOOLED” and other special cases.
  • sort_ord
    • Format: Integer between 0-99999, must be unique within the school table.
    • Example: 10
    • Default: none
    • Description: Controls sort order of items in the school drop-down list. Note: It is suggested that values be incremented by 10. This makes it easy to change the sort order and/or add additional schools if necessary.

zipcode table

The zipcode table is used to create the zipcode drop-down list. It must be populated when tracking by zipcode (config.program.useZipcode=1).

  • zipcode_id
    • Format: Unique number automatically assigned by MySQL.
    • Example: 1
    • Default: none
    • Description: System generated number. NOTE: Do not change this number as it is referenced by other tables.
  • desc
    • Format: Text
    • Example: 80031
    • Default: none
    • Description: Zipcode are generally specific zipcodes (80020, 80030, 80031), but can include other data as well.
  • sort_ord
    • Format: Integer between 0-99999, must be unique within the zipcode table.
    • Example: 10
    • Default: none
    • Description: Controls sort order of items in the zipcode drop-down list. Note: It is suggested that values be incremented by 10. This makes it easy to change the sort order and/or add additional zipcodes if necessary.

Navigation

Previous: Setting Up A ReadingRecord Site | Next: Upgrading ReadingRecord

 
docs/database_schema_information.txt (2858 views) · Last modified: 2013/04/04 17:21 by esisler