Difference between revisions of "MusicBrainz Database/Schema"

From MusicBrainz Wiki
m (Roadmap)
m
Line 1: Line 1:
 
== Objects ==
 
== Objects ==
  
=== Artist ===
+
=== [[Artist]] ===
  
An [[Artist|artist]] is generally a musician, group of musicians, a collaboration of multiple musicians or other music professional.
+
An artist is generally a musician, group of musicians, a collaboration of multiple musicians or other music professional.
  
=== Artist Credit ===
+
=== [[Artist Credit|Artist credit]] ===
  
 
List of artists, variations of artist names and pieces of text to join the artist names. Examples:
 
List of artists, variations of artist names and pieces of text to join the artist names. Examples:
Line 11: Line 11:
 
* "''[[Artist:0383dadf-2a4e-4d10-a46a-e9e041da8eb3|Queen]] & [[Artist:5441c29d-3602-4898-b1a1-b77fa23b8e50|David Bowie]]''" -- two artists ("Queen" and "David Bowie"), no name variations, joined with " & "
 
* "''[[Artist:0383dadf-2a4e-4d10-a46a-e9e041da8eb3|Queen]] & [[Artist:5441c29d-3602-4898-b1a1-b77fa23b8e50|David Bowie]]''" -- two artists ("Queen" and "David Bowie"), no name variations, joined with " & "
 
* "''[[Artist:86e2e2ad-6d1b-44fd-9463-b6683718a1cc|Jean-Michel Jarre]]*''" -- one artist ("Jean Michel Jarre"), name variation "Jean-Michel Jarre"
 
* "''[[Artist:86e2e2ad-6d1b-44fd-9463-b6683718a1cc|Jean-Michel Jarre]]*''" -- one artist ("Jean Michel Jarre"), name variation "Jean-Michel Jarre"
* "''[[Artist:4d78d8f5-f2e4-4eaa-86b2-952307aabd9f|Tracy W. Bush]], [[Artist:ef8d7a94-64a3-4362-b81f-b5999fb246de|Derek Duke]], [[Artist:7fd43bc8-b5ff-45ed-8ce5-1b0c17114a9e|Jason Hayes]] and [[Artist:2465d7da-7da3-42ea-8440-e701775d856b|Glenn Stafford]]''" -- four artists, no name variations, joined with ", ", ", ", ", " and " and ".
+
* "''[[Artist:4d78d8f5-f2e4-4eaa-86b2-952307aabd9f|Tracy W. Bush]], [[Artist:ef8d7a94-64a3-4362-b81f-b5999fb246de|Derek Duke]], [[Artist:7fd43bc8-b5ff-45ed-8ce5-1b0c17114a9e|Jason Hayes]] and [[Artist:2465d7da-7da3-42ea-8440-e701775d856b|Glenn Stafford]]''" -- four artists, no name variations, joined with commas and an "and".
  
=== Release Group ===
+
=== [[Release Group|Release group]] ===
  
Represents an abstract "album" entity. Technically it's a group of releases, with a specified type. The type is the same as current [[Release Type|release type]]. Examples:
+
Represents an abstract "album" entity. Technically it's a group of releases, with a specified type. Examples:
  
 
* Single "''Under Pressure''" by "''[[Artist:0383dadf-2a4e-4d10-a46a-e9e041da8eb3|Queen]] & [[Artist:5441c29d-3602-4898-b1a1-b77fa23b8e50|David Bowie]]''"
 
* Single "''Under Pressure''" by "''[[Artist:0383dadf-2a4e-4d10-a46a-e9e041da8eb3|Queen]] & [[Artist:5441c29d-3602-4898-b1a1-b77fa23b8e50|David Bowie]]''"
 
* Album "''The Wall''" by "''[[Artist:83d91898-7763-47d7-b03b-b92132375c47|Pink Floyd]]''"
 
* Album "''The Wall''" by "''[[Artist:83d91898-7763-47d7-b03b-b92132375c47|Pink Floyd]]''"
  
=== Release ===
+
=== [[Release]] ===
  
Real-world release object you can buy in your music store. It has release date and country, list of catalog number and label pairs, [[Next Generation Schema/Release Packaging|packaging type]] and release status. Example:
+
Real-world release object you can buy in your music store. It has release date and country, list of catalog number and label pairs, [[Next Generation Schema/Release Packaging|packaging type]] and release status. Examples:
  
 
* 1984 US release of "''The Wall''" by "''[[Artist:83d91898-7763-47d7-b03b-b92132375c47|Pink Floyd]]''", release on label "''Columbia Records''" with catalog number "C2K 36183" and UPC "074643618328", it's an official release and comes with two CDs in jewel case.
 
* 1984 US release of "''The Wall''" by "''[[Artist:83d91898-7763-47d7-b03b-b92132375c47|Pink Floyd]]''", release on label "''Columbia Records''" with catalog number "C2K 36183" and UPC "074643618328", it's an official release and comes with two CDs in jewel case.
Line 28: Line 28:
 
=== [[Medium]] ===
 
=== [[Medium]] ===
  
Piece of media, included in a release. Contains information about the format, position in the release and an optional title. Has attached CD TOCs. Example:
+
Piece of media, included in a release. Contains information about the format, position in the release and an optional title. Has attached CD TOCs. Examples:
  
 
* CD1 of the 1984 US release of "''The Wall''" by "''Pink Floyd''"
 
* CD1 of the 1984 US release of "''The Wall''" by "''Pink Floyd''"
Line 35: Line 35:
 
=== Tracklist ===
 
=== Tracklist ===
  
Simple list of tracks. Tracklists are used by mediums.
+
A tracklist is an ordered list of [[track]]s that is linked to one or more [[medium]]s. Examples:
 +
 
 +
* [http://musicbrainz.org/tracklist/703797 Tracklist 703797] appears on release [[Release:9d188442-bc79-4349-9516-92788978a4ca|The Wall]] (medium 1/2)
 +
* [http://musicbrainz.org/tracklist/1085862 Tracklist 1085862] appears on releases [[Release:4caf18ef-eaf1-44fd-9aff-889e763a4ac8|Violet Cries]] (digital media) and [[Release:0bce689a-c1e6-4d43-877a-afc0227d240a|Violet Cries]] (vinyl)
  
 
=== Track ===
 
=== Track ===
  
This object is not visible to users on its own, only in the context of a tracklist. It contains a link to a recording, title, artist credit and the position on the tracklist.
+
This object is not visible to users on its own, only in the context of a tracklist. It contains a link to a recording, title, artist credit and its position on its tracklist.
  
 
=== [[Recording]] ===
 
=== [[Recording]] ===
Line 49: Line 52:
 
* Remix "''Into the Blue (Underground mix)''" by "''Moby''"
 
* Remix "''Into the Blue (Underground mix)''" by "''Moby''"
  
=== Work ===
+
=== [[Work]] ===
  
 
One layer above recordings ("song", "composition", etc.). While recording represents audio data, work represents the composition behind the recording. Advanced Relationships should be used to link recording and work.
 
One layer above recordings ("song", "composition", etc.). While recording represents audio data, work represents the composition behind the recording. Advanced Relationships should be used to link recording and work.
Line 55: Line 58:
 
* Song "''Into the Blue''" by "''Moby''"  -- all the recordings listed above will be linked to this object
 
* Song "''Into the Blue''" by "''Moby''"  -- all the recordings listed above will be linked to this object
  
=== Label ===
+
=== [[Label]] ===
  
[[Label]]s represent mostly imprints.
+
Labels represent mostly imprints.
  
== Database Schema ==
+
== Schema ==
  
 
This diagrams shows the core database tables and relationships between them:
 
This diagrams shows the core database tables and relationships between them:
Line 67: Line 70:
 
Because we are adding new core entities, and there are plans for adding more, ARs between them need to be optimized. Only the absolutely necessary information is in l_*_* tables, everything else is shared:
 
Because we are adding new core entities, and there are plans for adding more, ARs between them need to be optimized. Only the absolutely necessary information is in l_*_* tables, everything else is shared:
  
{{:Next Generation Schema/Advanced Relationships Table Structure}}
+
===Advanced relationships table structure===
 
+
[[Image:ngs-ars.png|left|frameless|500px]]
 
 
  
== User Interface ==
+
There are tables for every possible combination of primary entities (artist, label, recording, release, release_group, url, work), all prefixed with ''l_'' and all with the same format. Two of them are shown in the diagram.  They contain a field, '''edits_pending''' that is a count of pending changes to the AR, and three foreign keys: '''link''' that points back to the associated entry in the ''link'' table, and '''entity0''' and '''entity1''' that point to the associated entry in the corresponding primary entity table (i.e. '''artist''', '''recording''', '''url''', etc.).
  
* [[Next Generation Schema/Release Editor]]
+
There are two tables that assist with avoiding unnecessary duplication:
* [[Next_Generation_Schema/Edit_User_Interface]]
 
  
== Web Service ==
+
The ''link'' table contains the begin and end date info, and the '''link_type''' foreign key field that specifies what kind of AR it is.  It also has a count of how many other attributes that particular link has in the '''attribute_count''' field, and a '''created''' field that specifies when it was created.
  
* [[Next Generation Schema/XML]]
+
Each AR attribute either applies to a particular AR or it doesn't. The ''link_attribute'' table stores this information, having a record for each '''attribute_type''' (a foreign key field for ''link_attribute_type'') of each '''link'''.
  
* [[Next Generation Schema/SearchServerXML]]
+
The AR types and attributes are defined in three other tables, shown at the top of the diagram.  They can only be modified by the AR editors.
  
* [[LinkedBrainz]]
+
The ''link_type'' table defines the types of ARs available. AR types are arranged in a number of trees, for ease of finding. This tree structure is expressed with the '''parent''' and '''child_order''' fields; '''parent''' is the '''id''' of the parent AR type, or null if it's at the root, and '''child_order''' orders the children of a given parent AR type. Each AR type has a unique uuid, stored in the ''gid'' field, for use in permalinks and external applications. The link between a particular AR type and the corresponding ''l_'' table is formed by the '''entity_type0''' and '''entity_type1''' fields.
  
== Roadmap ==
+
The attributes are themselves defined in the ''link_attribute_type'' table. Like AR types, attributes form a number of trees (the vast majority of them are individual musical instruments). Besides the '''parent''' and '''child_order''' fields shared with the ''link_type'' table, the ''link_attribute_table'' also has a '''root''' field,  showing the root of the tree that the attribute is part of.  Attributes also have '''name'''s and '''description'''s which appear various places they are displayed, as well as '''gid'''s and a '''last_updated''' timestamp.
  
See [[Next Generation Schema/Roadmap]].
+
The ''link_type_attribute_type'' table specifies what attributes can be applied to particular types of ARs; it has the necessary foreign key fields ('''link_type''' and '''attribute_type''') and it also specifies how many instances of the attribute (or one of it's children) can be added to the particular AR type in the '''min''' and '''max''' fields.  Currently, most of them allow the attributes to merely be present or absent, a few allow any number of copies of the attribute, or none. The "creative commons licensed download" attribute has to be included exactly once, while the ''instrument'' attribute (of the ''instrument'' type AR), requires at least one instance.
  
 
[[Category:Development]] [[Category:To Be Reviewed]] [[Category:WikiDocs Page]]
 
[[Category:Development]] [[Category:To Be Reviewed]] [[Category:WikiDocs Page]]

Revision as of 19:03, 24 March 2012

Objects

Artist

An artist is generally a musician, group of musicians, a collaboration of multiple musicians or other music professional.

Artist credit

List of artists, variations of artist names and pieces of text to join the artist names. Examples:

Release group

Represents an abstract "album" entity. Technically it's a group of releases, with a specified type. Examples:

Release

Real-world release object you can buy in your music store. It has release date and country, list of catalog number and label pairs, packaging type and release status. Examples:

  • 1984 US release of "The Wall" by "Pink Floyd", release on label "Columbia Records" with catalog number "C2K 36183" and UPC "074643618328", it's an official release and comes with two CDs in jewel case.

Medium

Piece of media, included in a release. Contains information about the format, position in the release and an optional title. Has attached CD TOCs. Examples:

  • CD1 of the 1984 US release of "The Wall" by "Pink Floyd"
  • CD2 of the 2005 UK release of "Aerial" by "Kate Bush", named "A Sky of Honey"

Tracklist

A tracklist is an ordered list of tracks that is linked to one or more mediums. Examples:

Track

This object is not visible to users on its own, only in the context of a tracklist. It contains a link to a recording, title, artist credit and its position on its tracklist.

Recording

Represents unique audio data. Has title, artist credit, duration, list of PUIDs and ISRCs. Examples (all are different Recordings):

  • Album version of the track "Into the Blue" by "Moby"
  • Remix "Into the Blue (Buzz Boys Main Room Mayhem mix)" by "Moby"
  • Remix "Into the Blue (Underground mix)" by "Moby"

Work

One layer above recordings ("song", "composition", etc.). While recording represents audio data, work represents the composition behind the recording. Advanced Relationships should be used to link recording and work.

  • Song "Into the Blue" by "Moby" -- all the recordings listed above will be linked to this object

Label

Labels represent mostly imprints.

Schema

This diagrams shows the core database tables and relationships between them:

ngs.png

Because we are adding new core entities, and there are plans for adding more, ARs between them need to be optimized. Only the absolutely necessary information is in l_*_* tables, everything else is shared:

Advanced relationships table structure

ngs-ars.png

There are tables for every possible combination of primary entities (artist, label, recording, release, release_group, url, work), all prefixed with l_ and all with the same format. Two of them are shown in the diagram. They contain a field, edits_pending that is a count of pending changes to the AR, and three foreign keys: link that points back to the associated entry in the link table, and entity0 and entity1 that point to the associated entry in the corresponding primary entity table (i.e. artist, recording, url, etc.).

There are two tables that assist with avoiding unnecessary duplication:

The link table contains the begin and end date info, and the link_type foreign key field that specifies what kind of AR it is. It also has a count of how many other attributes that particular link has in the attribute_count field, and a created field that specifies when it was created.

Each AR attribute either applies to a particular AR or it doesn't. The link_attribute table stores this information, having a record for each attribute_type (a foreign key field for link_attribute_type) of each link.

The AR types and attributes are defined in three other tables, shown at the top of the diagram. They can only be modified by the AR editors.

The link_type table defines the types of ARs available. AR types are arranged in a number of trees, for ease of finding. This tree structure is expressed with the parent and child_order fields; parent is the id of the parent AR type, or null if it's at the root, and child_order orders the children of a given parent AR type. Each AR type has a unique uuid, stored in the gid field, for use in permalinks and external applications. The link between a particular AR type and the corresponding l_ table is formed by the entity_type0 and entity_type1 fields.

The attributes are themselves defined in the link_attribute_type table. Like AR types, attributes form a number of trees (the vast majority of them are individual musical instruments). Besides the parent and child_order fields shared with the link_type table, the link_attribute_table also has a root field, showing the root of the tree that the attribute is part of. Attributes also have names and descriptions which appear various places they are displayed, as well as gids and a last_updated timestamp.

The link_type_attribute_type table specifies what attributes can be applied to particular types of ARs; it has the necessary foreign key fields (link_type and attribute_type) and it also specifies how many instances of the attribute (or one of it's children) can be added to the particular AR type in the min and max fields. Currently, most of them allow the attributes to merely be present or absent, a few allow any number of copies of the attribute, or none. The "creative commons licensed download" attribute has to be included exactly once, while the instrument attribute (of the instrument type AR), requires at least one instance.