Relational Database Model for DDMS 3.1

This document is an attempt to map the DDMS specification to a relational database model. The intent of this mapping is to be comprehensive first and pragmatic second -- the full scope of DDMS will be modeled, but some design decisions may be made for simplicity, such as modeling lists of values as a delimited string value. Although direct-to-table persistence mapping will probably not be a feature in any version of DDMSence, this table model may be useful when integrating DDMSence with an existing persistence framework like Hibernate or the Oracle XML SQL Utility (XSU).

General Notes

Column names generally match the XML element name, which is consistent with DDMSence's object model. There are a few minor differences (such as using "timePeriodName" in a temporalCoverage element to avoid confusing it with the plain XML element name).
The unique sequenced id value which is the primary key in each table should be unique across all tables. This will allow foreign key references in child components without requiring the child table to know what kind of parent it has. All id columns could also contain generated UUID values instead of sequenced integers.
Child elements will have links back to their parents, but not in the reverse direction. This key is allowed to have an initial <NULL> value, to support a bottom-up approach to building DDMS metacards from scratch.
If a table column is a character string and a value is not provided, an empty string should be favored instead of <NULL>.
The intent of the tables is to model the metacard data, not schema data. XML namespaces and other schema constructs are not necessarily modeled.
Reference tables (i.e. the four types of producers, or the valid names of ISM security attributes) are not included here. Columns which have string values for these constants could just as easily have foreign keys to a reference table.
Most validation constraints are omitted from this model, since it is assumed that a validating library like DDMSence would be placed in front of the tables.
Character string lengths are fairly arbitrary, although the numbers I chose are relatively reasonable for the types of data the fields contain. URI fields are set at 2048 characters to match Internet Explorer URL restrictions.
The pipe character, |, is suggested as a delimiter for columns containing lists in string form.

Table Overview

Primary DDMS Record

ddmsResource

Format Set

ddmsFormat
ddmsExtent

Resource Set

ddmsDates
ddmsIdentifier
ddmsLanguage
ddmsProducer
ddmsRights
ddmsRoleEntity
ddmsSource
ddmsSubtitle
ddmsTitle
ddmsType

Security Set

ddmsSecurity
ismSecurityAttribute

Summary Set

ddmsBoundingBox
ddmsBoundingGeometry
ddmsCategory
ddmsCountryCode
ddmsDescription
ddmsFacilityIdentifier
ddmsGeographicIdentifier
ddmsGeospatialCoverage
ddmsKeyword
ddmsLink
ddmsNonStateActor
ddmsPostalAddress
ddmsProductionMetric
ddmsRelatedResource
ddmsSrsAttributes
ddmsSubDivisionCode
ddmsSubjectCoverage
ddmsTemporalCoverage
ddmsVerticalExtent
ddmsVirtualCoverage
gmlPoint
gmlPolygon
gmlPosition

Extensible Layer

ddmsExtensibleAttribute
ddmsExtensibleElement

Primary DDMS Record

ddmsResource
In DDMS:	`ddms:Resource`
In DDMSence:	Resource
Parent Of:	ddmsDates, ddmsDescription, ddmsExtensibleElement, ddmsFormat, ddmsGeospatialCoverage, ddmsIdentifier, ddmsLanguage, ddmsProducer, ddmsRelatedResource, ddmsRights, ddmsSecurity, ddmsSource, ddmsSubjectCoverage, ddmsSubtitle, ddmsTemporalCoverage, ddmsTitle, ddmsType, and ddmsVirtualCoverage
Augmented By:	ddmsExtensibleAttribute, and ismSecurityAttribute
Additional Notes:	Please see the documentation on Extensible Attributes on a Resource to understand the ambiguity problem associated with modeling security attributes as extensible attributes.
Columns:
id	integer, not null, sequenced	primary key of this row. This value is a foreign key in all child component tables.
resourceElement	boolean	Whether this tag sets the classification for the XML file as a whole (required)
createDate	char(32)	the creation date (dates are stored in string format to ensure that the date value is retrieved in the same `xsd:date` that it was entered in).
desVersion	char(8)	the version of the ISM Data Encoding Specification used.
ddmsVersion	char(8)	the version of DDMS used.

The Format Set

ddmsFormat
In DDMS:	`ddms:format`
In DDMSence:	Format
Parent Of:	ddmsExtent
Augmented By:	No other tables.
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
mimeType	char(64), not null	the mime type, exactly 1 required
medium	char(64)	the medium, 0-1 optional

ddmsExtent
In DDMS:	`ddms:extent`
In DDMSence:	Extent
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	A qualifier is required when a value is present, but this constraint is not enforced here.
Columns:
id	integer, not null, sequenced	primary key of this row
formatId	integer	foreign key to the parent ddms:format element
qualifier	char(2048)	the qualifier URI
value	char(256)	the value

The Resource Set

ddmsDates
In DDMS:	`ddms:dates`
In DDMSence:	Dates
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	Dates are stored in string format, to ensure that a date value is retrieved in the same XML date format that it was entered in.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
created	char(32)	the creation date
posted	char(32)	the posting date
validTil	char(32)	the expiration date
infoCutOff	char(32)	the info cutoff date
approvedOn	char(32)	the approval date

ddmsIdentifier
In DDMS:	`ddms:identifier`
In DDMSence:	Identifier
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	Constraints on the qualifier and value are not enforced here.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
qualifier	char(2048), not null	the qualifier URI
value	char(256), not null	the value

ddmsLanguage
In DDMS:	`ddms:language`
In DDMSence:	Language.html
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	Constraints on the qualifier and value are not enforced here.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
qualifier	char(2048)	the qualifier URI
value	char(256)	the value

ddmsProducer
In DDMS:	`ddms:creator`, `ddms:contributor`, `ddms:pointOfContact`, and `ddms:publisher`
In DDMSence:	Creator, Contributor, PointOfContact, and Publisher
Parent Of:	ddmsRoleEntity
Augmented By:	ismSecurityAttribute
Additional Notes:	This modeling assumes that there is no reuse of producer entities between various roles. So, while the person named "Brian Uri" might have a creator role and a contributor role, and while Brian's details might be identical in each XML element, each set of details would have a separate row in this table.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
producerType	char(24)	the type of this producer, i.e. "creator", "contributor", "pointOfContact" or "publisher"

ddmsRights
In DDMS:	`ddms:rights`
In DDMSence:	Rights
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
privacyAct	boolean, default to "false"	whether protected by the Privacy Act
intellectualProperty	boolean, default to "false"	whether the resource has an intellectual property rights owner
copyright	boolean, default to "false"	whether the resource has a copyright owner

ddmsRoleEntity
In DDMS:	`ddms:Organization`, `ddms:Person`, `ddms:Service`, and `ddms:Unknown`
In DDMSence:	Organization, Person, Service, and Unknown
Parent Of:	ddmsSubOrganization
Augmented By:	ddmsExtensibleAttribute
Additional Notes:	All four entities share similar characteristics (at least one name, and optional phone numbers and email addresses), so they are grouped into a single table, rather than a separate table for each entity type. The latter approach is equally viable. This modeling also assumes that there is no reuse of entities between various roles. So, while the person named "Brian Uri" might have a creator role and an addressee role, and while Brian's details might be identical in each XML element, each set of details would have a separate row in this table.
Columns:
id	integer, not null, sequenced	primary key of this row
producerType	char(64)	the producer type (contributor, creator, pointOfContact, publisher)
producerId	integer	foreign key to the parent producer role fulfilled by this entity
entityType	char(24)	the type of this entity, i.e. "Organization", "Person", "Service" or "Unknown"
name	char(256), not null	a delimited string-list of names for this producer. At least one is required.
phone	char(256)	a delimited string-list of phone numbers for this producer. Optional.
email	char(2048)	a delimited string-list of email addresses for this producer. Optional.
surname	char(256)	This is a Person-specific column, containing a surname. Exactly one surname is required for Person records.
userId	char(64)	This is a Person-specific column, containing an ID for a user.
affiliation	char(256)	This is a Person-specific column, containing an organizational affiliation for a user. Optional.
acronym	char(64)	This is an Organization-specific column, containing an organizational acronym. Optional.

ddmsSource
In DDMS:	`ddms:source`
In DDMSence:	Source
Parent Of:	No other tables.
Augmented By:	ismSecurityAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
qualifier	char(2048), not null	the qualifier URI
value	char(256), not null	the value
schemaQualifier	char(64), not null	the value
schemaHref	char(2048), not null	the value

ddmsSubtitle
In DDMS:	`ddms:subtitle`
In DDMSence:	Subtitle
Parent Of:	No other tables.
Augmented By:	ismSecurityAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
value	char(2048)	the subtitle of the resource

ddmsTitle
In DDMS:	`ddms:title`
In DDMSence:	Title
Parent Of:	No other tables.
Augmented By:	ismSecurityAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
value	char(2048)	the title of the resource

ddmsType
In DDMS:	`ddms:type`
In DDMSence:	Type
Parent Of:	No other tables.
Augmented By:	ismSecurityAttribute
Additional Notes:	Constraints on the qualifier and value are not enforced here.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
qualifier	char(2048)	the qualifier URI
value	char(256)	the value

The Security Set

ddmsSecurity
In DDMS:	`ddms:security`
In DDMSence:	Security
Parent Of:	No other tables.
Augmented By:	ismSecurityAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
excludeFromRollup	boolean	has a fixed value of "true".

ismSecurityAttribute
In DDMS:	ISM Security Attributes
In DDMSence:	SecurityAttributes
Parent Of:	No other tables.
Augments:	ddmsCategory, ddmsDescription, ddmsGeospatialCoverage, ddmsKeyword, ddmsLink, ddmsProducer, ddmsRelatedResource, ddmsResource, ddmsSecurity, ddmsSource, ddmsSubjectCoverage, ddmsSubtitle, ddmsTemporalCoverage, ddmsTitle, ddmsType, and ddmsVirtualCoverage
Additional Notes:	Each row in this table represents a single ISM attribute, because the "complete" set of attributes is subject to change, and most values are optional.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent component of this attribute
name	char(64), not null	the unique attribute name, i.e. "classification" or "SCIcontrols"
value	char(2048)	the attribute value as a string

The Summary Set

ddmsBoundingBox
In DDMS:	`ddms:boundingBox`
In DDMSence:	BoundingBox
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	Longitude and latitude values may be negative, and will fall in a range of -180.0 to 180.0 for longitudes, and -90.0 to 90.0 for latitudes.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent geospatialCoverage element
WestBL	double	westbound longitude
EastBL	double	eastbound longitude
SouthBL	double	northbound latitude
NorthBL	double	southbound latitude

ddmsBoundingGeometry
In DDMS:	`ddms:boundingGeometry`
In DDMSence:	Bounding Geometry
Parent Of:	gmlPoint or gmlPolygon
Augmented By:	No other tables.
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent geospatialCoverage row of this attribute

ddmsCategory
In DDMS:	`ddms:category`
In DDMSence:	Category
Parent Of:	No other tables.
Augmented By:	ddmsExtensibleAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent subjectCoverage element
qualifier	char(2048)	the qualifier URI
code	char(2048)	the machine-readable representation of the category
label	char(2048), not null	the human-readable representation of the category

ddmsCountryCode
In DDMS:	`ddms:countryCode`
In DDMSence:	CountryCode
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	A country code might appear in a `ddms:geographicIdentifier` or `ddms:postalAddress` element.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent component of this attribute
qualifier	char(2048), not null	the qualifier URI
value	char(256), not null	the value

ddmsDescription
In DDMS:	`ddms:description`
In DDMSence:	Description
Parent Of:	No other tables.
Augmented By:	ismSecurityAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
value	char(2048)	the description of the resource

ddmsFacilityIdentifier
In DDMS:	`ddms:facilityIdentifier`
In DDMSence:	FacilityIdentifier
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent component of this attribute
beNumber	char(64), not null	unique identifier for a facility
osuffix	char(64), not null	used in conjunction with the beNumber to identify a facility

ddmsGeographicIdentifier
In DDMS:	`ddms:geographicIdentifier`
In DDMSence:	GeographicIdentifier
Parent Of:	ddmsCountryCode, ddmsFacilityIdentifier
Augmented By:	No other tables.
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent geospatialCoverage element
name	char(256)	a delimited string-list of names for this identifier
region	char(256)	a delimited string-list of region names for this identifier

ddmsGeospatialCoverage
In DDMS:	`ddms:geospatialCoverage`
In DDMSence:	GeospatialCoverage
Parent Of:	ddmsBoundingBox, ddmsBoundingGeometry, ddmsGeographicIdentifier, ddmsPostalAddress, or ddmsVerticalExtent
Augmented By:	ismSecurityAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource

ddmsKeyword
In DDMS:	`ddms:keyword`
In DDMSence:	Keyword
Parent Of:	No other tables.
Augmented By:	ddmsExtensibleAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent subjectCoverage element
value	char(256)	the keyword itself

ddmsLink
In DDMS:	`ddms:link`
In DDMSence:	Link
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent RelatedResource element
type	char(64), not null	the link type has a fixed value of "locator". It is being modelled in case this changes in the future.
href	char(2048), not null	the URL to the target resource
role	char(2048)	a URI reference describing the role of the link
title	char(2048)	a human-readable title
label	char(2048)	a name for the link, for use by an XLink arc-type element

ddmsPostalAddress
In DDMS:	`ddms:postalAddress`
In DDMSence:	PostalAddress
Parent Of:	ddmsCountryCode
Augmented By:	No other tables.
Additional Notes:	A postal address has either a state or a province, but not both.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent geospatialCoverage row of this attribute
street	char(2048)	a delimited string-list of street addresses.
city	char(64)	a city
state	char(64)	the state code, if within a country with states
province	char(64)	the province code, if within a country with provinces
postalCode	char(64)	the postal code of the address

ddmsRelatedResource
In DDMS:	`ddms:RelatedResource`
In DDMSence:	RelatedResource
Parent Of:	ddmsLink
Augmented By:	ismSecurityAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
relationship	char(2048), not null	a URI representing the relationship between the resource being described and these related resources
direction	char(64)	the direction of the relationship, which must have a value of "inbound", "outbound", or "bidirectional"
qualifier	char(2048), not null	the qualifier URI
value	char(256), not null	the value which describes the RelatedResource

ddmsSrsAttributes
In DDMS:	GML SRS Attributes
In DDMSence:	SRSAttributes
Parent Of:	No other tables.
Augments:	gmlPoint, gmlPolygon, and gmlPosition
Additional Notes:	Unlike the ISM Security Attributes table, where each row is an attribute, the rows in this table represent a complete set of SRS information for a single element. Because the required/optional status of each attribute varies based on the parent, no constraints enforce any rules here.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent component of this attribute
srsName	char(2048)	the URI-based SRS name, optional on Positions, but required on Points and Polygons
srsDimension	integer	a positive integer dimension
axisLabels	char(2048)	an ordered list of axes labels, as a space-delimited list of NCNames
uomLabels	char(2048)	an ordered list of unit-of-measure labels for the axes, as a space-delimited list of NCNames

ddmsSubjectCoverage
In DDMS:	`ddms:subjectCoverage`
In DDMSence:	SubjectCoverage
Parent Of:	ddmsCategory or ddmsKeyword
Augmented By:	ismSecurityAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource

ddmsTemporalCoverage
In DDMS:	`ddms:temporalCoverage`
In DDMSence:	TemporalCoverage
Parent Of:	No other tables.
Augmented By:	ismSecurityAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
timePeriodName	char(64)	the name of the time period
start	char(32)	the start date string in a valid XML date format, or one of the strings, "Not Applicable" or "Unknown".
end	char(32)	the end date string in a valid XML date format, or one of the strings, "Not Applicable" or "Unknown".

ddmsVerticalExtent
In DDMS:	`ddms:verticalExtent`
In DDMSence:	VerticalExtent
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	As described in the class description, DDMSence requires the top-level unitOfMeasure and datum attributes to be consistent on both the parent component and the two extents, so this information need only be stored once for a table row.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent geospatialCoverage row of this attribute
unitOfMeasure	char(64), not null	should be one of Meter, Kilometer, Foot, StatuteMile, NauticalMile, Fathom, Inch
datum	char(64), not null	should be one of MSL, AGL, or HAE
minVerticalExtent	double, not null	a decimal number representing the minimum extent
maxVerticalExtent	double, not null	a decimal number representing the maximum extent

ddmsVirtualCoverage
In DDMS:	`ddms:virtualCoverage`
In DDMSence:	VirtualCoverage
Parent Of:	No other tables.
Augmented By:	ismSecurityAttribute
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
address	char(2048)	a network address
protocol	char(64)	a network protocol for data transfer

gmlPoint
In DDMS:	`gml:Point`
In DDMSence:	Point
Parent Of:	gmlPosition
Augmented By:	ddmsSrsAttributes
Additional Notes:	No other notes.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent boundingGeometry element of this attribute
gmlId	char(64), not null	a unique ID for the point

gmlPolygon
In DDMS:	`gml:Polygon`
In DDMSence:	Polygon
Parent Of:	gmlPosition
Augmented By:	ddmsSrsAttributes
Additional Notes:	Because a polygon is comprised of ordered positions that create an enclosed area, it is assumed that positions are ordered according to their `index` value. As a closed shape, the first and last positions should be identical, but no validation is done on this constraint here. The last position should definitely be a separate row in the gmlPosition table.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent boundingGeometry element of this attribute
gmlId	char(64), not null	a unique ID for the polygon

gmlPosition
In DDMS:	`gml:pos`
In DDMSence:	Position
Parent Of:	No other tables.
Augmented By:	ddmsSrsAttributes
Additional Notes:	The `index` column is intended for positions that are part of a polygon, and should be 0-based.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent component of this attribute
index	integer	a 0-based index for an ordered set of positions
latitude	double, not null	first coordinate
longitude	double, not null	second coordinate
heightAboveEllipsoid	number	optional third coordinate

The Extensible Layer

ddmsExtensibleAttribute
In DDMS:	Custom Attributes
In DDMSence:	ExtensibleAttributes
Parent Of:	No other tables.
Augments:	ddmsCategory, ddmsKeyword, ddmsProducer, and ddmsResource
Additional Notes:	Each table row represents a single custom attribute.
Columns:
id	integer, not null, sequenced	primary key of this row
parentId	integer	foreign key to the parent component of this attribute
qname	char(256), not null	the qualified name of the attribute, i.e. "opensearch:relevance"
value	char(2048)	the attribute value as a string

ddmsExtensibleElement
In DDMS:	Custom Elements
In DDMSence:	ExtensibleElement
Parent Of:	No other tables.
Augmented By:	No other tables.
Additional Notes:	This table contains the custom elements which can appear at the top-level of the DDMS resource. The table merely stores the XML of the element (DDMSence does not dig into these elements either). Each row in this table should map to one top-level extensible element. Also note that the XML namespaces may have been defined higher up, so the XML fragment may not be correct on its own.
Columns:
id	integer, not null, sequenced	primary key of this row
resourceId	integer	foreign key to the parent DDMS resource
xml	clob	the raw XML text of the extensible element