Administrator access

Enter the site-wide access code to view pgfacts.org.

Simple gate for now; not a full login system.
Contact Us
Work In Progress: database/DCMS_Database_Schema.md Define the structure of the database.
What is DCMS? How the database-backed content and menu system supports site management. Knowledge Base Search PG information, trials, providers, research opportunities, and care resources using everyday questions. Proof of Concept A working prototype of the database-backed site, navigation, and content model.

DCMS - Dynamic Content and Menuing System

Database Schema

This document describes the database schema for AllThingsPG SQL Server database. Schema is the structure of the database, for tables, stored procedures, functions, constraints and much more. Schema is the definition of the database. All databases have schema, which is a description of things like table columns, type etc. A table in a database is much like an Excel spreadsheet, where each column has a name and type (integer, text, currency etc). The definition of a table defines what type of data can be put in that table, and where to put it. This document and the related database pages are developer references; they focus on the implemented schema and data model.

The SQL project at AllThingsPG.Database is the implementation baseline for table names, column definitions, constraints, relationships, views, functions, procedures, and deployment behavior. It is the master source of truth. Even the database itself is not, it is the schema defined in the database project that is. Like the POC and eventual Phase 2 website, the db project is in a Github repository and securely stored with our nonprofit All Things PG organization (or will be soon).

The schema defines various categories of tables:

  • lookup data that defines valid domain values
  • menu and route definitions used to build navigation
  • content documents and typed content elements (the schema does not currently store an element SortOrder)
  • staging records used before content is published
  • master menu, menu items, portal menu
  • registration, communication, emails, contacts
  • system settings, error log
  • test harness and staging

Database Project

Property Value
Project `AllThingsPG.Database`
Project file `AllThingsPG.Database.sqlproj`
Schema provider `Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider`
Target SQL Server database
Default schema `dbo`
Deployment SSDT build, pre-deployment, and post-deployment scripts

Implemented object inventory

s
Object type Location Objects
Tables `Tables` 29 tables, including operational, lookup, staging, and test tables
Views `Views` `ContentDocumentList`, `ContentElementList`, `MenuItemList`, `MissingTableList`, `TableList`
Functions `Functions` 15 scalar/table-valued utility and validation functions
Stored procedures `Stored Procedures` 61 initialization, CRUD, validation, population, logging, and reporting procedures
Deployment scriptsProject root and `Scripts` `Pre-Deployment.sql`, `Post-Deployment.sql`, backup and initial-data scripts
Test scripts `Testing` Non-build scripts for setup, normalization, population, validation, and test execution

1. Lookup Tables

Lookup tables provide validated domain values referenced by transactional tables. Their codes are deliberately compact because they are used as foreign-key values in menu, content, registration, staging, and rendering records.

Table: VisitorType

VisitorType defines the personas that can be selected by the application and used by menu visibility rules. Populate_VisitorType clears and reseeds these five rows.

Code Description SortOrder IsDefault
P Patient 1 0
C Caregiver 2 0
M Provider 3 0
D Pharmaceutical 4 0
V Guest 5 1

A VisitorMask is normalized against these lookup rows. ALL expands to the current codes in lookup order, which is PCMDV. The MenuItem and StagingMenuItem check constraints also allow the character R, but R is not a VisitorType inserted by the current population routine. RouteType R separately means Redirect.

Table: RouteType

Defines how a menu item route is interpreted by the application.

Column Type Nullability Constraints / purpose
`Code` `char(1)` NOT NULL Primary key
`Description` `nvarchar(200)` NOT NULL Route behavior description
`Folder` `nvarchar(200)` NOT NULL Application folder or handler context
`SortOrder` `int` NOT NULL Display or evaluation order

Current sample data:

Code Description Folder SortOrder
`V` Visitor `Visitor` 1
`C` Content `Content` 2
`P` Page `Page` 3
`R` Redirect `Redirect` 4

Table: ContentType

Defines the document-level content classification or rendering template.

Column Type Nullability Constraints / purpose
`Code` `char(1)` NOT NULL Primary key
`Name` `nvarchar(50)` NOT NULL Short type name
`Description` `nvarchar(200)` NOT NULL Type description
`SortOrder` `int` NOT NULL Display order

Current sample data:

Code Name Description SortOrder
`A` Article Long-form educational content 1
`N` NewsItem News updates and announcements 2
`F` FAQ Question and answer pairs 3
`T` Testimonial Patient and caregiver stories 4
`P` Profile Board members, staff, medical experts 5
`E` Event Events, webinars, fundraisers 6
`L` Link External resource or reference 8
`R` Research Clinical trial or research study 9
`X` External External file 10
`G` Gallery Image gallery metadata 11
`D` Document PDF or downloadable file reference 12
`U` Unknown Content has not been populated 99

Table: FormatType

Defines the source or encoding format of a content element.

Column Type Nullability Constraints / purpose
`Code` `char(1)` NOT NULL Primary key
`Name` `nvarchar(50)` NOT NULL Format name
`Description` `nvarchar(200)` NOT NULL Format description
`FileTypes` `nvarchar(200)` NOT NULL Associated file extensions or types
`SortOrder` `int` NOT NULL Display or processing order

Current sample data:

Code Name Description SortOrder
`T` Text Plain Text 1
`R` RTF Rich Text 2
`H` HTML Web Page Markup 3
`E` EPUB Electronic Pub 4
`P` PDF Portable Doc 5
`M` MD Markdown 6
`J` JSON JSON 7
`I` IMAGE Binary Image 8
`A` AUDIO Binary Audio 9
`W` WORD MS Word Document 10
`S` EXCEL MS Spreadsheet 11
`N` PPT Powerpoint 12
`G` GDOC Google Document 13
`D` OPEN Open Document 14
`?` UNKNOWN Unknown 99

Table: MimeType

Defines the media type and rendering characteristics of a content element.

Column Type Nullability Constraints / purpose
`Code` `char(1)` NOT NULL Primary key
`FormatType` `char(1)` NOT NULL FK to `FormatType.Code`
`Name` `nvarchar(50)` NOT NULL MIME type name
`Description` `nvarchar(200)` NOT NULL Rendering description
`IsText` `bit` NOT NULL Indicates text payload
`IsBinary` `bit` NOT NULL Indicates binary payload
`IsRenderable` `bit` NOT NULL Indicates whether the application can render it
`SortOrder` `int` NOT NULL Processing or display order

Constraint: FK_MimeType_FormatType references FormatType(Code).

Current sample data:

Code FormatType Name Description IsText IsBinary IsRenderable SortOrder
`T` `T` TXT `text/plain` 1 0 1 1
`M` `M` MD `text/markdown` 1 0 1 2
`H` `H` HTML `text/html` 1 0 1 3
`C` `H` HTM `text/htm` 1 0 1 4
`R` `R` RTF `application/rtf` 1 0 1 5
`J` `J` JSON `application/json` 1 0 1 6
`E` `E` EPUB `application/epub+zip` 0 1 1 7
`P` `P` PDF `application/pdf` 0 1 1 8
`I` `I` JPG `image/jpeg` 0 1 1 9
`N` `I` PNG `image/png` 0 1 1 10
`F` `I` GIF `image/gif` 0 1 1 11
`S` `I` SVG `image/svg+xml` 0 1 1 12
`W` `I` WEBP `image/webp` 0 1 1 13
`A` `A` MP3 `audio/mpeg` 0 1 1 14
`U` `A` WAV `audio/wav` 0 1 1 15
`Q` `A` OGG `audio/ogg` 0 1 1 16
`D` `W` DOCX `application/vnd.openxmlformats-officedocument.wordprocessingml.document` 0 1 1 17
`X` `W` DOCM `application/vnd.ms-word.document.macroEnabled.12` 0 1 1 18
`Y` `W` DOTX `application/vnd.openxmlformats-officedocument.wordprocessingml.template` 0 1 1 19
`Z` `W` DOTM `application/vnd.ms-word.template.macroEnabled.12` 0 1 1 20
`L` `S` XLS `application/vnd.ms-excel` 0 1 1 21
`K` `S` XLSX `application/vnd.openxmlformats-officedocument.spreadsheetml.sheet` 0 1 1 22
`!` `N` PPT `application/vnd.ms-powerpoint` 0 1 1 23
`@` `N` PPTX `application/vnd.openxmlformats-officedocument.presentationml.presentation` 0 1 1 24
`#` `N` PPS `application/vnd.ms-powerpoint` 0 1 1 25
`$` `N` PPSX `application/vnd.openxmlformats-officedocument.presentationml.slideshow` 0 1 1 26
`%` `N` POT `application/vnd.ms-powerpoint` 0 1 1 27
`^` `N` POTX `application/vnd.openxmlformats-officedocument.presentationml.template` 0 1 1 28
`&` `N` PPTM `application/vnd.ms-powerpoint.presentation.macroEnabled.12` 0 1 1 29
`*` `N` PPSM `application/vnd.ms-powerpoint.slideshow.macroEnabled.12` 0 1 1 30
`+` `N` POTM `application/vnd.ms-powerpoint.template.macroEnabled.12` 0 1 1 31
`G` `G` GDOC `application/vnd.google-apps.document` 0 1 1 32
`O` `D` ODT `application/vnd.oasis.opendocument.text` 0 1 1 33
`B` `?` BIN `application/octet-stream` 0 1 0 99

Table: WorkflowStatus

Defines the editorial state of content and staged content.

Column Type Nullability Constraints / purpose
`Code` `char(1)` NOT NULL Primary key
`Title` `nvarchar(50)` NOT NULL Short status title
`Description` `nvarchar(200)` NULL Status explanation
`SortOrder` `int` NOT NULL Workflow order

Current sample data:

Code Title Description SortOrder
`D` Draft Content is newly curated or created 1
`R` Review Content needs review 2
`A` Approved Content has been approved but not published 3
`X` Rejected Content has been rejected 4
`P` Published Content has been published 5

2. Menu and Routing Tables

Table: MenuItem

MenuItem is the runtime menu model. A row represents one menu node. Parent-child relationships are represented by the self-referencing ParentMenuItemID. A NULL parent identifies a top-level item.

Column Type Nullability Constraints / purpose
`MenuItemID` `int identity(1,1)` NOT NULL Clustered primary key
`ParentMenuItemID` `int` NULL Self-FK to `MenuItem.MenuItemID`
`Title` `nvarchar(200)` NOT NULL Menu label
`RouteType` `char(1)` NOT NULL FK to `RouteType.Code`
`RouteTarget` `nvarchar(200)` NOT NULL Route-specific target value
`VisitorMask` `varchar(10)` NOT NULL Allowed visitor-type codes
`SortOrder` `int` NOT NULL Defaults to `0`; must be non-negative
`IsActive` `bit` NOT NULL Defaults to `1`; controls visibility
`CreatedDate` `datetime2(0)` NOT NULL Defaults to `sysdatetime()`
`ModifiedDate` `datetime2(0)` NOT NULL Defaults to `sysdatetime()`

Constraints:

  • FK_MenuItem_Parent references the same table through ParentMenuItemID.
  • FK_MenuItem_RouteType references RouteType(Code).
  • CK_MenuItem_SortOrder requires SortOrder >= 0.
  • CK_MenuItem_Title_Normalized rejects reserved URL, markup, and query characters.
  • CK_MenuItem_VisitorMask permits only P, C, M, D, V, and R, and requires a non-empty mask.

Current MenuItem data from MenuItemList

The current menu has a separate runtime MenuItem row for each navigation choice. RouteTarget is stored as a route string. For a content route, its target parameter contains the linked ContentDocumentID.

MenuItemID ParentMenuItemID Title RouteType RouteTarget VisitorMask ContentDocumentID IsPublished
1 NULL Welcome V /visitor?target=Guest PCMDV — —
2 1 Patient V /visitor?target=Patient PCMDV — —
8 NULL About PG C /content?target=1 PCMDV 1 0
9 8 What is PG C /content?target=2 PCMDV 2 0
18 17 Emotional Journey C /content?target=11 PCMDV 11 0
25 24 Emotional Journey C /content?target=18 P 18 0
32 31 Emotional Journey C /content?target=25 C 25 0
38 NULL Being a Provider C /content?target=31 M 31 0
39 38 Early Diagnosis C /content?target=32 M 32 0
72 67 Clinic Finder P /page?target=clinicfinder.cshtml PCMDV — —
74 73 All Things Pyoderma 4.2k members R /redirect?target=https://www.facebook.com/groups/368163159232 PCMDV — —

The repeated Emotional Journey title demonstrates the audience-aware design. The general menu item and the patient and caregiver menu items each have a different MenuItemID and ContentDocumentID. When articles are authored, the three records can hold distinct content while retaining the same familiar title.

In this snapshot all content-route documents are placeholders: ContentType U (Unknown), WorkflowStatus D (Draft), and IsPublished = 0. The database contains no authored PG articles or published content at this time.

Table: MasterMenu

MasterMenu stores the descriptive master-menu representation used by import, population, or legacy menu workflows. It is separate from the normalized runtime MenuItem hierarchy.

Column Type Nullability Constraints / purpose
`MenuID` `int identity(1,1)` NOT NULL Identity identifier
`ParentMenuID` `int` NULL Parent reference value
`Parent` `nvarchar(100)` NOT NULL Parent menu label
`Child` `nvarchar(100)` NOT NULL Child menu label
`SubMenu` `nvarchar(100)` NOT NULL Submenu label
`MenuType` `nvarchar(10)` NOT NULL Menu classification
`VisitorMask` `nvarchar(20)` NULL Visitor visibility mask
`RouteType` `nvarchar(10)` NULL Route classification
`RouteTarget` `nvarchar(200)` NULL Route target
`Description` `nvarchar(200)` NOT NULL Menu description
`Notes` `nvarchar(200)` NULL Additional notes

Tables: MasterMenuStage and MasterMenu_Stage

These tables support staged or imported master-menu data. They retain the parent, child, submenu, menu type, visitor, routing, description, and notes fields before or during menu population.

MasterMenuStage has an identity MenuID and uses MenuType. MasterMenu_Stage has an identity LoadOrder and uses Type; it does not define a primary key or foreign keys.

3. Content Tables

Table: ContentDocument

Stores the document-level descriptor associated with a menu item. A content document is the parent of zero or more typed ContentElement rows. The current schema does not persist their editorial order.

Column Type Nullability Constraints / purpose
`ContentDocumentID` `int identity(1,1)` NOT NULL Clustered primary key
`MenuItemID` `int` NOT NULL FK to `MenuItem.MenuItemID`
`ContentType` `char(1)` NOT NULL FK to `ContentType.Code`
`Description` `nvarchar(500)` NOT NULL Document description
`MetaData` `nvarchar(max)` NOT NULL JSON document metadata
`WorkflowStatus` `char(1)` NOT NULL Defaults to `D`; FK to `WorkflowStatus.Code`
`IsPublished` `bit` NOT NULL Public visibility flag
`PublishedDate` `datetime2(0)` NULL Publication timestamp
`CreatedDate` `datetime2(0)` NOT NULL Defaults to `sysdatetime()`
`ModifiedDate` `datetime2(0)` NOT NULL Defaults to `sysdatetime()`

Constraints:

  • CK_ContentDocument_MetaData_IsJSON requires valid JSON in MetaData.
  • FK_ContentDocument_MenuItem cascades deletion from a menu item.
  • FK_ContentDocument_ContentType and FK_ContentDocument_WorkflowStatus enforce valid classifications.

Table: ContentElement

Stores a content block belonging to a document. The table currently has no persisted SortOrder column, so editorial block order is not yet defined by the schema.

Column Type Nullability Constraints / purpose
`ContentElementID` `int identity(1,1)` NOT NULL Clustered primary key
`ContentDocumentID` `int` NOT NULL FK to `ContentDocument.ContentDocumentID`
`FormatType` `char(1)` NOT NULL FK to `FormatType.Code`
`MimeType` `char(1)` NOT NULL FK to `MimeType.Code`
`BlockInfo` `nvarchar(max)` NOT NULL JSON block metadata
`TextContent` `nvarchar(max)` NULL Text, HTML, or other textual payload
`BinaryContent` `varbinary(max)` NULL Binary payload
`LocalFilePath` `nvarchar(255)` NULL Local server file reference
`ExternalFilePath` `nvarchar(255)` NULL External file reference
`CreatedDate` `datetime2(0)` NOT NULL Defaults to `sysdatetime()`
`ModifiedDate` `datetime2(0)` NOT NULL Defaults to `sysdatetime()`

Constraint: CK_ContentElement_BlockInfo_IsJSON requires valid JSON in BlockInfo. Deleting a document cascades to its elements.

4. Staging Tables

Staging tables represent content before it is promoted to the operational content tables.

Table: StagingDocument

Column Type Nullability Constraints / purpose
`StagingDocumentID` `int identity(1,1)` NOT NULL Clustered primary key
`MenuItemID` `int` NULL Optional FK to `MenuItem.MenuItemID`
`Title` `nvarchar(255)` NOT NULL Staged document title
`Description` `nvarchar(500)` NOT NULL Staged document description
`ContentType` `char(1)` NOT NULL FK to `ContentType.Code`
`Metadata` `nvarchar(max)` NOT NULL JSON staging metadata
`WorkflowStatus` `char(1)` NOT NULL Staging workflow state
`CreatedDate` `datetime2(0)` NOT NULL Defaults to `sysdatetime()`
`ModifiedDate` `datetime2(0)` NOT NULL Defaults to `sysdatetime()`

CK_StagingDocument_Metadata_IsJSON validates Metadata. The menu-item relationship is optional so a staged document can exist before it is assigned to navigation.

Table: StagingElement

Stores staged content blocks associated with a staged document.

Column Type Nullability Constraints / purpose
`StagingElementID` `int identity(1,1)` NOT NULL Clustered primary key
`StagingDocumentID` `int` NOT NULL FK to `StagingDocument.StagingDocumentID`
`WorkflowStatus` `char(1)` NOT NULL Element workflow state; FK to `WorkflowStatus.Code`
`FormatType` `char(1)` NOT NULL FK to `FormatType.Code`
`MimeType` `char(1)` NOT NULL FK to `MimeType.Code`
`Metadata` `nvarchar(max)` NOT NULL JSON rendering metadata
`TextContent` `nvarchar(max)` NULL Staged text payload
`BinaryContent` `varbinary(max)` NULL Staged binary payload
`LocalFilePath` `nvarchar(255)` NULL Local file reference
`ExternalFilePath` `nvarchar(255)` NULL External file reference
`CreatedDate` `datetime2(0)` NOT NULL Defaults to `sysdatetime()`
`ModifiedDate` `datetime2(0)` NOT NULL Defaults to `sysdatetime()`

CK_StagingElement_Metadata_IsJSON validates Metadata. The staging document relationship is not configured with cascade delete.

5. Registration and Communication Tables

Table: Registration

Stores registered users and visitor-persona preferences.

Column Type Nullability Constraints / purpose
`RegistrationID` `int identity(1,1)` NOT NULL Clustered primary key
`Email` `nvarchar(500)` NOT NULL Unique login/contact address
`PasswordHash` `varbinary(256)` NULL Password hash
`PasswordSalt` `varbinary(256)` NULL Password salt
`VisitorType` `char(1)` NOT NULL FK to `VisitorType.Code`
`IsMember` `bit` NOT NULL Membership flag
`IsVolunteer` `bit` NOT NULL Volunteer flag
`IsNewsLetter` `bit` NOT NULL Newsletter subscription flag
`IsFundraising` `bit` NOT NULL Fundraising communication flag
`IsActive` `bit` NOT NULL Account-active flag
`CreatedDate`, `ModifiedDate` `datetime2(0)` NOT NULL Audit timestamps

UQ_Registration_Email enforces unique email addresses.

Table: Contact

Stores contact and address details for a registration.

Column Type Nullability Purpose
`ContactID` `int identity(1,1)` NOT NULL Primary key
`RegistrationID` `int` NOT NULL FK to `Registration.RegistrationID`
`FirstName`, `LastName`, `MiddleName` `varchar(100)` First and last names are NOT NULL; middle name is NULL Name fields
`MobilePhone`, `HomePhone`, `WorkPhone` `varchar(20)` NULL Phone fields
`WorkEmail` `varchar(500)` NULL Work email
`AddressLine1`, `AddressLine2` `varchar(200)` NULL Address fields
`ForeignAddress` `varchar(500)` NULL Non-domestic address
`City`, `StateProvince`, `Country` `varchar(100)` NULL Location fields
`PostalCode` `varchar(20)` NULL Postal code
`DateOfBirth` `date` NULL Birth date
`CreatedDate`, `ModifiedDate` `datetime2(0)` NOT NULL Audit timestamps

Table: Email

Stores outbound email messages.

Column Type Nullability Purpose
`EmailID` `int identity(1,1)` NOT NULL Primary key
`Subject` `varchar(200)` NOT NULL Subject
`BodyText` `nvarchar(max)` NULL Message body
`SentDate` `datetime2(7)` NOT NULL Defaults to `sysutcdatetime()`
`CreatedDate`, `ModifiedDate` `datetime2(0)` NOT NULL Audit timestamps

Table: EmailRecipient

Associates an email with a registered recipient.

Column Type Nullability Purpose
`EmailRecipientID` `int identity(1,1)` NOT NULL Primary key
`EmailID` `int` NOT NULL FK to `Email.EmailID`; cascade delete
`RegistrationID` `int` NOT NULL FK to `Registration.RegistrationID`; cascade delete
`DateSent` `datetime2(7)` NULL Delivery timestamp
`CreatedDate` `datetime2(0)` NOT NULL Creation timestamp

Table: EmailAttachment

Stores binary files attached to an email.

Column Type Nullability Purpose
`EmailAttachmentID` `int identity(1,1)` NOT NULL Primary key
`EmailID` `int` NOT NULL FK to `Email.EmailID`; cascade delete
`FileName` `varchar(255)` NOT NULL File name
`FileType` `varchar(50)` NOT NULL File type
`FileData` `varbinary(max)` NOT NULL File contents
`CreatedDate` `datetime2(0)` NOT NULL Creation timestamp

Tables: Newsletter and PublishedNewsletter

Newsletter associates a ContentDocument with distribution and publication dates. PublishedNewsletter stores published delivery representations, including optional email recipient and URL references, with flags for email, HTML, and flyer forms.

6. System and Utility Tables

Table: SystemSettings

Stores typed application and database configuration values.

Column Type Nullability Purpose
`SettingID` `int identity(1,1)` NOT NULL Primary key
`SettingKey` `varchar(50)` NOT NULL Unique uppercase setting key
`SettingType` `varchar(50)` NOT NULL Uppercase type such as `STRING`, `JSON`, `GUID`, `INTEGER`, or `BOOLEAN`
`SettingGroup` `varchar(50)` NOT NULL Uppercase group: `GLOBALVAR`, `APPLICATION`, `DATABASE`, or `SYSTEM`
`SettingValue` `sql_variant` NULL Typed setting value
`LastUpdated` `datetime2(7)` NOT NULL Defaults to `sysdatetime()`

The table enforces uppercase keys, valid setting types and groups, JSON and GUID validity where applicable, required values for non-string/list types, and a unique setting key.

Table: SystemSettings_Audit

Stores historical setting values and update timestamps. It contains AuditID, the original setting identity and key/type/group/value fields, LastUpdated, and AuditDate.

Table: SystemTables

Registers database tables for system discovery and diagnostics.

Column Type Nullability Constraints / purpose
`TableID` `int identity(1,1)` NOT NULL Primary key
`TableName` `varchar(50)` NOT NULL Unique table name
`TableType` `varchar(15)` NOT NULL `Registration`, `Menu`, `Staging`, `Content`, `Lookup`, or `System`
`SortOrder` `int` NOT NULL Display order

Table: Numbers

Stores integer values used by database loops and set-based utility procedures. Number is the clustered primary key.

Table: ErrorLog

Stores database diagnostics. It includes ProcedureName, error details, serialized Parameters, executing UserName and HostName, a constrained LogLevel (DEBUG, INFO, WARNING, or ERROR), and LogDate.

7. Test-Support Tables

These tables support database validation and test harness execution. They are included in the database project but are not part of the public runtime content model.

Table: TestHarness

Stores test cases and execution results, including test hierarchy, category, expected and actual values, menu/content identifiers, processing state, and error details.

Table: MenuItemTestHarness

Stores menu-specific test definitions and outcomes, including parent test relationships, expected results, menu title, route values, visitor mask, sort order, active state, processing state, and notes.

8. Views

Provides a flattened menu reporting and validation view. It includes the current menu item, parent menu attributes, route metadata, associated content-document metadata, parent content metadata, parent/child indicators, and a MissingPlaceholder flag when a content route lacks a document.

ContentDocumentList

Joins ContentDocument to its menu item, route type, content type, and workflow status. It is intended for content validation, administration, and reporting.

ContentElementList

Joins each ContentElement to its document, menu item, route type, content type, format type, MIME type, and workflow status. It exposes both element data and inherited document/menu metadata.

TableList

Provides database table inventory information for system discovery.

MissingTableList

Reports expected or registered tables that are missing from the database.

9. Functions

The project defines the following reusable functions:

  • Create_MetaData
  • Get_SystemSetting
  • Get_TableList
  • Normalize_ErrorMessage
  • Normalize_RouteTarget
  • Normalize_RouteType
  • Normalize_SettingKey
  • Normalize_Slug
  • Normalize_Title
  • Normalize_Token
  • Normalize_VisitorMask
  • SplitCSVRow
  • TableExists
  • TablesExists
  • Validate_Url

Normalization functions enforce consistent values before validation or persistence. Discovery functions expose settings and table metadata. Existence and validation functions support deployment and database-health checks.

10. Stored Procedures

Stored procedures are organized by responsibility:

Responsibility Procedures
Initialization and deployment `Initialize_Database`, `Create_Tables`, `Create_Lookup_Tables`, `Create_Content_Tables`, `Create_Staging_Tables`, `Create_System_Tables`, `Create_Registration_Tables`, `Create_Numbers_Table`, `Create_MenuItem_Table`, `Create_MasterMenu_Table`, `Create_ErrorLog_Table`, `Create_SystemSettings_Table`, `Create_SystemTables_Table`, `Create_TableList_Table`
Content `Insert_ContentDocument`, `Insert_ContentElement`, `Insert_Content_Placeholder`, `Delete_Content`, `Populate_ContentDocument`
Menu `Insert_MenuItem`, `Insert_MenuItem_Parent`, `Insert_MenuItem_Child`, `Delete_MenuItem`, `Populate_MenuItems`, `Populate_MasterMenu`, `Perform_MenuItem_Validation`
Staging `Populate_Staging`, `Delete_Staging`, `Validate_Staging`
Lookup population `Populate_ContentType`, `Populate_FormatType`, `Populate_MimeType`, `Populate_RouteType`, `Populate_VisitorType`, `Populate_WorkflowStatus`, `Populate_Lookup_Tables`
System data `Populate_SystemSettings`, `Populate_SystemTables`, `Populate_Numbers_Table`, `Set_SystemSetting`, `Get_SystemInfo`
Validation and inspection `Validate_Database`, `Validate_Content`, `Validate_MenuItem`, `Perform_Table_Check`, `Show_Tables`, `Show_Schema`, `Show_Foreign_Keys`
Logging and messaging `Log_Error`, `Log_Debug`, `Clear_ErrorLog`, `Print_Messages`, `Opening_Message`, `Success_Message`
Test support `Create_TestHarness`, `Populate_TestHarness`, `Execute_TestHarness`, `Create_TempTable_FromCSV`

The Testing folder contains non-build versions and focused scripts for setup, population, normalization, URL validation, menu validation, schema display, and test execution.

11. Deployment and Data Flow

  1. SSDT builds the schema objects listed in the project file.
  2. Pre-Deployment.sql and Scripts\PreDeploy\01_Backup.sql provide pre-deployment preparation and backup behavior.
  3. Lookup, system, menu, and initial content data are populated through post-deployment scripts and population procedures.
  4. Imported or authored content enters StagingDocument and StagingElement.
  5. Validation procedures check staged menu, content, workflow, route, format, and metadata values.
  6. Approved content is represented by ContentDocument and its ContentElement children.
  7. Runtime menu queries use active MenuItem rows, visitor masks, route metadata, and sort order.
  8. Runtime content queries use published ContentDocument rows and their associated elements.
  9. Views provide flattened data for administration, diagnostics, and validation.

Runtime relationship summary

VisitorType
    |
Registration ---- Contact
    |
Email ---- EmailRecipient ---- Registration
  |
  +---- EmailAttachment

RouteType ---- MenuItem ---- ContentDocument ---- ContentElement
                  |                 |
                  |                 +---- ContentType
                  +---- parent MenuItem
                  +---- VisitorMask
                  +---- WorkflowStatus

ContentType ---- StagingDocument ---- StagingElement
FormatType ---- ContentElement / StagingElement
MimeType   ---- ContentElement / StagingElement
WorkflowStatus ---- ContentDocument / StagingElement

12. Source of Truth

This document describes the current SQL project, but the .sql object definitions remain authoritative when a description and implementation differ. Any future schema change should update the corresponding SQL object and this document in the same change.

Back to top