The table layout mirrors the MusicXML schema definition, so it's a good idea to have the schema at hand when looking at the table reference.

There are a large number of tables in the schema, so there isn't a single diagram that includes all of the tables. Each table in the schema has its own diagram in the subpages of this page. There's also a high-level layout on the subpages of "Database Layout".

The table diagrams were made using MySQL Workbench.

Many one-to-one foreign key relationships in the MusicXML schema are shown in the diagrams as one-to-many relationships which MySQL Workbench draws by default. I haven't changed any of these relationships in the diagram, and instead have left the relationships as Workbench has drawn them. Refer to the MusicXML schema definition for the correct relationships.

Table names are, generally speaking, the name of the element, group, or complex type defined in the MusicXML schema, unless a table name would be an SQL reserved word.

When a table represents something unnamed (an xs:choice block, for example), I've given the table a name of my own choice that I feel represents the structure.

A number of tables hold data of several different types of objects, but have a common parent object type. These tables can be thought of as abstract tables whose common subtypes are all stored in the same table.

This relationship of an abstract parent type and concrete subtypes is implemented as a single-inheritance table, using a discriminator column whose value indicates the concrete subtype of that record in the table.

All discriminator columns have a column name of the table name plus _type.

The discriminator value stored in the column is the lower case element name with a space replacing any dash.

A prominent discriminator column example is the music_data table whose music_data_type field has values note, direction, barline for elements note, direction, barline, respectively.

Tables with a discriminator column have fields that only one or some of the subclasses use, since all of the subclass definitions are combined into a single table. For example, the duration field in the music_data table is used by note, backup, forward, and figured bass records, but not by direction and attribute records.

Field names are taken from the attribute or element name the field represents, unless a field name would be an SQL reserved word. Some field names are renamed to avoid ambiguity.

Generally speaking, element text is stored in a field named value, and an element's type attribute is in field type, unless there is a data type conflict or ambiguity.

All tables have a primary key column id.

When the MusicXML schema defines an id attribute (except for the optional-unique-id attribute group), the field is the table name plus _id.

The name of a foreign key (with the exception of a foreign key to a single-inheritance table) is the table name plus _id.

The name of a foreign key to a single-inheritance table uses the discriminator value of the joined record, with underscores replacing spaces, plus _id. Examples: a foreign key to a note in the music_data table is note_id; a foreign key to a figured bass in the music_data table is figured_bass_id.

Many MusicXml elements include an id attribute. These values are stored in the relevant table's element_id field.

Several tables have an ordering column.

XML elements that have a list of subelements of the same name rely on preserving the order of the subelements to preserve the correct data in the original MusicXML document. Notes in a measure, for example, have a different meaning when placed in the document in a different order.

The SQL standard does not guarantee a fetch order for a query unless one is given in an "order by" clause, hence the ordering column.

When a MusicXML file is converted to a database record in the tasks application, lists of same-name subelements are stored in tables that have an ordering column using an incrementing index with an initial value of 1, and then storing the index value in the ordering column of the database record. When a MusicXML score is retrieved from the database, these records are retrieved ordered by the ordering column value.

Boolean values are stored in char(1) fields with values Y or N.

MusicXML schema yes-no attribute types are stored as a boolean: Y for yes, N for no, and null when the attribute is not present in the element.

When a subelement is defined as an empty type, the presence or absence of the element is stored as a boolean value Y or N.