Association Table Reference

Association Table Reference

Purpose

This table is a lookup table that enables mapping between users, roles, groups and blogs. It should be noted that the group field and the group values under type are hold overs from Movable Type where they were provided by Movable Type Enterprise. Thus Melody users should disregard those fields for the time being. They are not yet considered deprecated, but they are not used either.

Associated Perl classes

• MT::Association

Fields

• association_id - integer not null auto_increment. - The primary key of the association table.
• association_type - integer not null. - The type of the association. Value choices are:
  • 1 - User:Blog:Role Association
  • 2 - Group:Blog:Role Association
  • 3 - User:Group Association
  • 4 - User:Role Association
  • 5 - Group:Role Association
• association_author_id - integer. - The ID of the user in the association. Comes from mt_author.
• association_blog_id - integer. - The ID of the blog for the association.
• association_created_by - integer. - The user (an administrator) who created the association.
• association_created_on - datetime. - The date and time when the association was created.
• association_group_id - integer. - The group ID for the association.
• association_modified_by - integer. - The user who last modified the association.
• association_modified_on - datetime. - The date and time when the association was last modified.
• association_role_id - integer. - The ID of the role in the association.

##Indexes

• mt_association_blog_id
• mt_association_author_id
• mt_association_role_id
• mt_association_group_id
• mt_association_type
• mt_association_created_on

SQL Example

SELECT
    author_name 
    blog_name 
    role_name
FROM
    mt_author a
JOIN
    mt_association assoc
ON
    a.author_id = assoc.association_author_id
JOIN
    mt_blog b
ON
    assoc.association_blog_id = b.blog_id
JOIN
    mt_role r
ON
    r.role_id = assoc.association_role_id;