Next Generation Schema/Advanced Relationships Table Structure
Advanced Relationships Table Structure
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.