Paul M. Jones

An “association table” is a table that joins other tables in a many-to-many relationship. For example, if an Article can have more than one Tag, and each Tag can be placed on one or more Articles, then they are in a many-to-many relationship. To associate them to each other, we need a third table through which we can join them.

-- one end of a many-to-many relationship
CREATE TABLE article (
    article_id INT,
    title VARCHAR(255),
    body TEXT
);

-- the other end of a many-to-many relationship
CREATE TABLE tag (
    tag_id INT,
    name VARCHAR(255)
);

-- an association table mapping articles and tags to each other
CREATE TABLE article_tag (
    article_tag_id INT,
    article_id INT,
    tag_id INT
);

In ORM terms, we might say each Article “has many” Tags “through” the ArticleTag association, that each Tag also “has many” Articles “through” the ArticleTag, and finally that each ArticleTag “belongs to” an Article and that it “belongs to” a Tag.

When writing SQL to find the Tags for an Article, or to find all the Articles that use a specific Tag, we join the ArticlesTags table to get the associated entity IDs. The SQL looks something like the following:

-- select all the tags for an article
SELECT tag.*
FROM tag
JOIN article_tag ON article_tag.tag_id = tag.tag_id
WHERE article_tag.article_id = ?

-- select all the articles that use a tag
SELECT article.*
FROM article
JOIN article_tag ON article_tag.article_id = article.article_id
WHERE article_tag.tag_id = ?

This pattern is called an association table mapping.

On the other hand, a “pivot table” is a cross-tabulation query, frequently used in spreadsheets. You can see more about pivot tables through Google. In short, the idea is to build a query, and convert the rows into columns by grouping the rows in a particular way. These kinds of queries generally involve some conditionals and calculations to group the query results; you can see some examples here.

In summary: if you are joining tables to each other in a many-to-many relationship, the table that maps the relationship is an association table. If you are doing a cross-tabulation to convert rows into columns, you are working with pivot table.

UPDATE: Apparently "pivot" is also a keyword in SQL Server to help generate true pivot tables; see here. See also Associative Entity.

Read the Reddit discussion about this post here.