ALTA Database Tables

ALTA’s internal MSSQL database server is “DB1” at Hostek. There are 4 databases, details on each and tables below.

alta

• ALTA’s oldest and definitely contains some defunct tables and info, some very old.

• Primarily now the source of the Intranet/CMS.

DataWarehouse

• Primarily the copy of the flattened overnight tables from the Impexium Report server, which then drive Thaddeus as well as web site directories, etc.

• Contains some parts of the nightly scripts.

Registry

• Contains the raw confirmation data (copy of old GoMembers “cncatcod” moved here. Registry 2.0 changes structure.

• Contains the nightly snapshots and change logs which drive the nightly push of changes to the Registry, as well as the scripts for those processes.

gmProd

• The GoMembers database in the state before Impexium migration.

• Key data was moved in “History” tables.

• An older version of Thaddeus points to this, but has been deactivated since it’s not used, but we wouldn’t delete this old data should we ever need to get into something that wasn’t migrated, however unlikely.

alta

Many tables here are no longer used, but we don’t typically delete anything like this, so any not noted below are either not currently used, never used, replaced, etc.

• altaJobXXX

  • CMS for web site job postings.

• bannerXXX / enewsXXX

  • Ad system before Revive.

• bbData/Values

  • Holds static Board Board data.

• bestPracXXX

  • Hold best practices data, files, etc.

• CEXXXX

  • Holds the CE specific uploads.

• cgXXX

  • Claims Guide documents

• commXXX

  • Holds the Committee (Engagement Group) documents.

• eliteXXX

  • Holds the Elite Applications and data

• events

  • Holds events not yet in Impexium to merge into alta.org/events calendar

• fileXXX / staffFiles

  • Holds “Staff Files”

• formXXX

  • Holds Policy Forms data

• homeXXX

  • Various home page “what’s new” types of things.

• HOPXXX

  • Holds the HOP resources

• impOptionsXXX

  • Stores the API information and tokens as updated for SSO, etc.

• ListEdits

  • Has the settings and fields for various lists managed on the Intranet

• navXXX

  • The core of the CMS….navigation, but all content as well.

  • nav2023 is current main CMS.

• newsXXX

  • Holds all articles

• proceedings

  • Meeting proceedings before 2015, new in WordPress.

• missing2023 / searches2023

  • Log of searchings and 404s

• MPXXX

  • Stores the Marketplace extras

• regImportXXX

  • Holds the bulk registry import tool data.

• registryXXX

  • Holds the RMS materials, FAQs, etc.

• tilnXXX

  • TILJ documents

• tirsXXX

  • TIRS documents

• topicXXX

  • Holds the new topic pages from CMS.

• utilityXXX

  • Top bar links changed by role from I am a…. or login member type, etc.

DataWarehouse

See Impexium Custom Data Tables to start.

Most ALTA_XXXX tables are copied nightly from Report Server to here for faster use. Noted here are those which are different from just a copy after flattening.

• ALTA_AdvocacyTransactions

  • Create from a RS View.

• ALTA_BranchCompliance

  • Similar to master UberCompliance, but for when memberships/PFLs are on a branch, either in error, or after a merge/change.

• Calculated (SPROC - CalculatedMake)

  • Old “Uber Compliance” concept.

• DailySnapshot (SPROC - DailySnapshotMake)

  • Saves the membership status every night. Just basics of ID and type.

• DDC_nightly

  • Old for DDC data push.

• SPROC - DDCPush / DDCPush Trans

  • Flattens individual, org, and $$ and FTPs to DDC for processing nightly.

• Designations

  • Old data, now in Impexium.

• Elevate_XXXX

  • Four tables that are refreshed from the Intranet as necessary with all Elevate data via their API.

• ImpexiumLog

  • Not used.

• MeeingsSpeakersWP / MeetingsWP

  • Not used.

• MemberRanges

  • Holds the data for how to split the different member types into ranges for Dashboards

• MemberTypes

  • Old. Now “ALTA_MemberTypes” from Report Server

• NightlyLog

  • Log of nightly script process after Report Server, which are logged there.

• RAP_XXX

  • Not used, RAP was never finalized.

• Relationships

  • Not used.

• YearlyStats

  • Static numbers for Dashboard.

  • Manually add a row each year after October 1 to lock down Universe. See Membership and Licensing - Yearly Tasks

• ZIPCodes

  • Not used, old USPS data.

• SPROC - QuorumPush

  • Nightly flattening and FTP of TAN data to Quorum.

• SPROC - RSDataTables

  • Nightly that does the copy of tables from RS to DW

• SPROC - TANCheckbox

  • Nightly to process TAN/TIPAC checkboxes from subscriptions

• SPROC - VV Push

  • Not used, was for Voter Voice’s API push, replaced by Quorum.

• SPROC - ZipCodeRadius

  • Not used, can figure out distances from zip code centers from “ZipCodes” Lat/Long data.

Registry

• _ALTA_Registry_archive_tbl_names

  • Logs the nightly scripts numbering, date, time, etc. for the snapshots and change logs.

• _ALTA_Registry_Pushes

  • Logs the nightly push and response. Can be viewed from RMS Admin.

• ALTA_Registry_Address_XXXX / Entity / Role / Verification

  • The current, “Yesterday”, and past nightly snapshots.

  • Automatically deleted after 6 months.

• ALTA_Registry_Address_LOG_XXXX / Entity / Role / Verification

  • The change logs, created by compare “today” to “yesterday”, and source of the nightly push.

  • Kept forever.

• cncatcod

  • Copy of the GM table storing confirmation details. Changing in Registry 2.0

• confirmations

  • New data structure for confirmation details for Registry 2.0

• eoDataFields / eoFileData / eoFileLocations / eoFiles / eoFileTypes

  • Categorization and data for E&O documents

• settings

  • A single value for turning on or off email notifications RMS Wide.

• SPROC - ALTA_Registry_NEW_Evening

  • Start of the overnight processes, rotates the tables, triggers snapshot. (See Nightly Steps.xlsx)

• SPROC - ALTA_Registry_NEW_Evening1

  • Triggers snapshots

  • Triggers Address cleanup script

• SPROC - ALTA_Registry_NEW_Snapshots

  • Creates 4X “today” tables (see above)

• SPROC - ALTA_Registry_NEW_Evening2

  • Triggered after cleanup scripts.

  • Triggers removing old snapshots.

  • Triggers change log creation.

  • Triggers push script and final cleanup steps.

• SPROC - ALTA_Registry_Del_Old_Snapshots

  • Removes 4X tables older than 6 months.

• SPROC - ALTA_Registry_logs

  • Compares 4X snapshots just created with “Yesterday” to create changes to push.