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.

Main Tables

This page describes the 25 tables listed in the database’s SystemTables catalog and shows representative rows. The TableType values are the classifications used by the project. Lookup sections show every row defined by the current seed scripts. Other examples come from the supplied schema extract when available; rows labeled Illustrative explain the table’s shape and are not live database records.

Tablename	TableType	Purpose
SystemTables	System	Lists database tables with their catalog category and order.
SystemSettings	System	Stores application and database settings.
Numbers	System	Stores integers from 1 through 1,000 for utility use.
ErrorLog	System	Records database messages and errors.
RouteType	Lookup	Names the kinds of menu destinations.
VisitorType	Lookup	Defines visitor audiences.
ContentType	Lookup	Classifies content documents.
FormatType	Lookup	Defines content block formats and file extensions.
MimeType	Lookup	Defines media types and rendering characteristics.
WorkflowStatus	Lookup	Defines content workflow states.
MasterMenu	Menu	Holds the imported source menu.
TestHarness	Menu	Stores menu test cases and results.
MenuItem	Menu	Holds the website's navigation items and route targets.
PortalMenu	Menu	Holds visitor-specific portal tiles.
BoardMember	Menu	Holds board member names, roles, biographies, and images.
ContentDocument	Content	Holds a page's content description and publication state.
ContentElement	Content	Holds text, media, or file content blocks.
StagingDocument	Staging	Holds a content document being prepared.
StagingElement	Staging	Holds content blocks being prepared.
Registration	Registration	Holds a person's email, visitor type, and participation choices.
Contact	Registration	Holds contact and address details for a registration.
Newsletter	Registration	Connects a newsletter to its content and dates.
Email	Registration	Holds an email subject, body, and sent date.
EmailRecipient	Registration	Connects an email to a registered recipient.
EmailAttachment	Registration	Holds a file attached to an email.

System tables

SystemTables

Catalog of the 25 tables classified on this page. This is a representative sample; the complete list appears above.

TableName	TableType	SortOrder
SystemTables	System	11
VisitorType	Lookup	22
MenuItem	Menu	33
ContentDocument	Content	41
Registration	Registration	61

SystemSettings

Stores typed settings such as site configuration, menu source, and feature choices. These rows are from the database project’s baseline settings.

SettingKey	SettingType	SettingGroup	SettingValue
SITE_NAME	STRING	SYSTEM	AllThingsPG
MASTER_MENU	STRING	SYSTEM	C:\ATPG\Master_Menu.csv
ENABLE_REGISTRATION	BOOLEAN	APPLICATION	TRUE
DEFAULT_PAGE_SIZE	INTEGER	GLOBALVAR	25
SUPPORT_EMAIL	STRING	GLOBALVAR	support@allthingspg.org

Numbers

Contains the integers 1 through 1,000.

Number
1
2
3
…
1,000

ErrorLog

Records routine name, message, severity, parameters, and execution context. Illustrative row:

ProcedureName	ErrorMessage	LogLevel	LogDate
Populate_MenuItems	Example validation message	ERROR	Example timestamp

Lookup tables

RouteType

Identifies how a menu item is opened. All current seed values:

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

VisitorType

Defines the visitor types available to the site. All current seed values:

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

ContentType

Classifies a content document. All current seed values:

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

FormatType

Defines a content block’s format and associated file extensions. All current seed values:

Code	Name	Description	FileTypes
T	Text	Plain Text	.txt
R	RTF	Rich Text	.rtf
H	HTML	Web Page Markup	.html, .htm
E	EPUB	Electronic Pub	.epub
P	PDF	Portable Doc	.pdf
M	MD	Markdown	.md
J	JSON	JSON	.json
I	IMAGE	Binary Image	.gif, .jpg, .svg, .webp, .png
A	AUDIO	Binary Audio	.mp3, .wav, .ogg
W	WORD	MS Word Document	.docx, .docm, .dotx, .dotm
S	EXCEL	MS Spreadsheet	.xls, .xlsx
N	PPT	Powerpoint	.ppt, .pptx, .pps, .ppsx, .pot, .potx, .pptm, .ppsm, .potm
G	GDOC	Google Document	.gdoc
D	OPEN	Open Document	.odt
?	UNKNOWN	Unknown	.bin

MimeType

Identifies the media type and whether content is text, binary, or renderable. All current seed values:

Code	FormatType	Name	Description	Text	Binary	Renderable
T	T	TXT	text/plain	1	0	1
M	M	MD	text/markdown	1	0	1
H	H	HTML	text/html	1	0	1
C	H	HTM	text/htm	1	0	1
R	R	RTF	application/rtf	1	0	0
J	J	JSON	application/json	1	0	0
E	E	EPUB	application/epub+zip	0	1	0
P	P	PDF	application/pdf	0	1	1
I	I	JPG	image/jpeg	0	1	1
N	I	PNG	image/png	0	1	1
F	I	GIF	image/gif	0	1	1
S	I	SVG	image/svg+xml	1	0	1
W	I	WEBP	image/webp	0	1	1
A	A	MP3	audio/mpeg	0	1	1
U	A	WAV	audio/wav	0	1	1
Q	A	OGG	audio/ogg	0	1	1
D	W	DOCX	application/vnd.openxmlformats-officedocument.wordprocessingml.document	0	1	0
X	W	DOCM	application/vnd.ms-word.document.macroEnabled.12	0	1	0
Y	W	DOTX	application/vnd.openxmlformats-officedocument.wordprocessingml.template	0	1	0
Z	W	DOTM	application/vnd.ms-word.template.macroEnabled.12	0	1	0
L	S	XLS	application/vnd.ms-excel	0	1	0
K	S	XLSX	application/vnd.openxmlformats-officedocument.spreadsheetml.sheet	0	1	0
!	N	PPT	application/vnd.ms-powerpoint	0	1	0
@	N	PPTX	application/vnd.openxmlformats-officedocument.presentationml.presentation	0	1	0
#	N	PPS	application/vnd.ms-powerpoint	0	1	0
$	N	PPSX	application/vnd.openxmlformats-officedocument.presentationml.slideshow	0	1	0
%	N	POT	application/vnd.ms-powerpoint	0	1	0
^	N	POTX	application/vnd.openxmlformats-officedocument.presentationml.template	0	1	0
&	N	PPTM	application/vnd.ms-powerpoint.presentation.macroEnabled.12	0	1	0
*	N	PPSM	application/vnd.ms-powerpoint.slideshow.macroEnabled.12	0	1	0
+	N	POTM	application/vnd.ms-powerpoint.template.macroEnabled.12	0	1	0
G	G	GDOC	application/vnd.google-apps.document	0	1	0
O	D	ODT	application/vnd.oasis.opendocument.text	0	1	0
B	?	BIN	application/octet-stream	0	1	0

WorkflowStatus

Defines the content workflow stage. All current seed values:

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
S	Staging	Content is linked to a staged menu item	6

MasterMenu

Imported source menu entries, including hierarchy labels, route, audience, description, and notes. Illustrative row:

MenuID	Parent	Child	Title	MenuType	RouteType	RouteTarget	VisitorMask
1		Welcome	Welcome	P	V	/visitor?target=Guest	PCMDV

TestHarness

Stores menu validation cases and their expected/actual results. Illustrative row:

TestID	Title	RouteType	Expected	Actual	IsError
1	Welcome	V	PASS	PASS	0

MenuItem

The navigation records used by the site. For content routes, RouteTarget contains the associated ContentDocumentID. The examples below are grouped by route type and come from the supplied menu extract.

Visitor routes (V)

MenuItemID	ParentMenuItemID	Title	RouteTarget	VisitorMask
1	NULL	Welcome	/visitor?target=Guest	PCMDV
2	1	Patient	/visitor?target=Patient	PCMDV

Content routes (C)

MenuItemID	Title	RouteTarget	VisitorMask	ContentDocumentID
8	About PG	/content?target=1	PCMDV	1
25	Emotional Journey	/content?target=18	P	18
32	Emotional Journey	/content?target=25	C	25
39	Early Diagnosis	/content?target=32	M	32

Page routes (P)

MenuItemID	Title	RouteTarget	VisitorMask
72	Clinic Finder	/page?target=clinicfinder.cshtml	PCMDV

Redirect routes (R)

MenuItemID	Title	RouteTarget	VisitorMask
74	All Things Pyoderma 4.2k members	/redirect?target=https://www.facebook.com/groups/368163159232	PCMDV

The repeated Emotional Journey title represents general, patient, and caregiver pages with separate menu and content IDs. The content rows shown are unpublished placeholders, not authored articles.

PortalMenu

Stores tiles shown for individual visitor types: title, short description, icon path, and linked menu item. Illustrative row:

MenuItemID	Title	Description	IconPath	VisitorType
25	Emotional Journey	Understand the emotional impact of PG — from fear and frustration to strength and acceptance.	/img/portal-icons/Patient.svg	P

BoardMember

Stores board member names, roles, positions, descriptions, photos, and display order. These names and roles come from the database seed procedure.

Name	Role	Position	PictureFileName	SortOrder
Dr. Alex Ortega Loayza	Founder	Medical Director	Founder.jpg	1
Corlis Watkins-Nass	President	Marketing	President.jpg	2
Dave DePottie	Chairman	Information Technology	Chairman.jpg	3

Content tables

ContentDocument

One document-level record is associated with a menu item. Current extract examples are placeholders with ContentType U (Unknown), WorkflowStatus D (Draft), and IsPublished 0.

ContentDocumentID	MenuItemID	ContentType	Description	WorkflowStatus
1	8	U	About PG	D
18	25	U	Emotional Journey	D
25	32	U	Emotional Journey	D

ContentElement

Stores a content block’s format, media type, block information, and text, binary, or file content. No authored content elements appear in the supplied extract. Illustrative row:

ContentElementID	ContentDocumentID	FormatType	MimeType	BlockInfo	TextContent
1	1	H	H	{"block":"body"}	Example page text

Staging tables

StagingDocument

Holds a content document while it is being prepared. No staging rows are included in the supplied extract. Illustrative row:

StagingDocumentID	StagingMenuItemID	ContentType	Description	WorkflowStatus
1	8	A	About PG draft	S

StagingElement

Holds a text, media, or file block associated with a staged document. Illustrative row:

StagingElementID	StagingDocumentID	WorkflowStatus	FormatType	MimeType	TextContent
1	1	S	H	H	Example staged text

Registration tables

Registration

Stores the email address, visitor type, optional account credentials, and participation preferences. Illustrative row:

RegistrationID	Email	VisitorType	IsMember	IsVolunteer	IsNewsLetter	IsFundraising	IsActive
1	person@example.org	P	0	0	1	0	1

Contact

Stores a registrant’s name and optional contact and address details. Illustrative row:

ContactID	RegistrationID	FirstName	LastName	City	StateProvince	Country
1	1	Example	Person	Portland	Oregon	United States

Connects a content document to a newsletter description and its distribution and publication dates. Illustrative row:

NewsletterID	ContentDocumentID	Description	NewsletterType	DistributionDate	PublishDate
1	10	Community research update	N	2026-10-01	2026-10-01

Email

Stores the subject and body of a message and when it was sent. Illustrative row:

EmailID	Subject	BodyText	SentDate
1	Research update	A short message to registered recipients.	2026-10-01

EmailRecipient

Connects a message to a registration and can record when it was sent. Illustrative row:

EmailRecipientID	EmailID	RegistrationID	DateSent
1	1	1	2026-10-01

EmailAttachment

Stores an attached file’s name, type, and binary contents; it may also refer to a newsletter. Illustrative row:

EmailAttachmentID	EmailID	NewsLetterID	FileName	FileType
1	1	1	newsletter.pdf	application/pdf

Other SQL project tables

These supporting, migration, audit, and legacy tables are not assigned a TableType in SystemTables. Their examples are illustrative because the supplied data extract does not include rows for them.

EmailRequest

Holds a requested message, its processing state, and the resulting EmailID when processed.

EmailRequestID	RegistrationID	RequestType	Subject	IsProcessed	ProcessedEmailID
1	1	N	Research update	0	—

MasterMenu_Stage

Menu-import staging rows with a load order and source menu fields.

LoadOrder	Parent	Child	SubMenu	Type	RouteType	RouteTarget
1		Welcome		Parent	V	/visitor?target=Guest

MasterMenuStage

Holds imported menu fields in the second menu-import staging table.

MenuID	Parent	Child	SubMenu	RouteType	RouteTarget	VisitorMask
1		Welcome		V	/visitor?target=Guest	PCMDV

MenuItemTestHarness

Stores menu-specific test cases, expected outcomes, and processing results.

TestID	Category	Title	RouteType	Expected	Result	IsError
1	MENU	Welcome	V	PASS	—	0

MigrationDocument

Holds captured legacy page titles, content classifications, descriptions, and metadata.

MigrationDocumentID	MenuTitle	ContentType	Description	MetaData
1	About PG	A	Legacy About PG page	{}

MigrationElement

Holds a captured text or media block associated with a legacy document.

MigrationElementID	MigrationDocumentID	FormatType	MimeType	TextMigration
1	1	H	H	Example legacy page content

MigrationMapping

Connects an old menu title to its destination MenuItemID and records whether the match is correct.

MigrationMappingID	OldMenuTitle	MenuItemID	IsCorrect
1	About PG	8	1

PublishedNewsletter

Records newsletter delivery forms and an optional recipient or URL reference.

PublishedNewsletter	EmailRecipientID	URL	IsEmail	IsHtml	IsFlyer
1	1	/newsletters/research-update	1	1	0

save_menuitem

Contains a second set of menu-item fields. The project does not document its specific use; illustrative row:

MenuItemID	ParentMenuItemID	Title	RouteType	RouteTarget	VisitorMask	IsActive
1	—	Welcome	V	/visitor?target=Guest	PCMDV	1

StagingMenuItem

Holds a menu hierarchy and routes before they become active MenuItem records.

StagingMenuItemID	ParentMenuItemID	Title	RouteType	RouteTarget	VisitorMask	SortOrder
1	—	Welcome	V	/visitor?target=Guest	PCMDV	1

SystemSettings_Audit

Keeps prior setting values and the dates they were last updated and audited.

AuditID	SettingID	SettingKey	SettingType	SettingValue	AuditDate
1	1	SITE_NAME	STRING	AllThingsPG	Example timestamp

For field definitions, constraints, and foreign keys, see the database schema.

Administrator access