Difference between revisions of "Next Generation Schema/Advanced Relationships Table Structure"

From MusicBrainz Wiki
Jump to navigationJump to search
(3 intermediate revisions by 2 users not shown)
Line 1: Line 1:
#REDIRECT [[MusicBrainz_Database/Schema#Relationship_table_structure]]
===Advanced Relationships Table Structure===
The main table is ''link''. It 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.
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. Four of them are shown in the diagram above. They contain a field, '''modpending''' 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.).
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 attributes are themselves defined in the ''link_attribute_type'' table. Attributes form a number of trees (the vast majority of them are individual musical instruments). This tree structure is expressed with the '''parent''', '''root''' and '''child_order''' fields; '''parent''' is the '''id''' of the parent attribute, or null if it's at the root; '''root''' is the root of the tree the attribute is part of, and '''child_order''' orders the children of a given parent attribute. Attributes also have '''name'''s and '''description'''s which appear various places they are displayed, as well as '''gid'''s (TODO: why?) 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.

Latest revision as of 12:18, 24 May 2015