What’s The Difference Between A “Pivot Table” And An “Association Table”?

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.

Are you stuck with a legacy PHP application? You might like my book because it gives you a step-by-step guide to improving your codebase, all while keeping it running the whole time.
Share This!Share on Google+Share on FacebookTweet about this on TwitterShare on RedditShare on LinkedIn

23 thoughts on “What’s The Difference Between A “Pivot Table” And An “Association Table”?

  1. Back in the day, what you call “Association Tables”, we used to call “Join Tables” or “Jump Tables”.

    “Association Tables” were always just “XTab or “Cross Tabulation” tables. (and they were always a bitch to build. 🙂

    Cheers!
    =C=

  2. I was curious what caused you to feel like the two concepts needed disambiguation and write this. I had never heard of a situation where either of these was or could be confused with one another, but then on a hunch, I googled for Laravel and pivot tables (I don’t have Laravel experience but knew of previous terminology debates).

    I think I found my answer.

    • Yeah it’s “another” attack on Laravel. Mention of framework isn’t necessary at all, we (Laravel users) all knew exactly what he meant upon reading the title. I mean if all that’s wrong with the framework is the selection of a few words then Taylor must be doing something right. There’s been little to no critique of the framework at a technical or architectural level (doing so would be simple, no framework is perfect, “everything is a tradeoff” etc, but it’s far easier/lazier just to attack terminology. I would go so far as to say that architectural criticisms would probably be well received).

      Aura may well be “technically more correct” and I get the feeling that Paul believes he should somehow have framework supremacy on that basis alone, and can’t stand that one guy who isn’t “php old guard” writes a simple little framework and becomes the go to framework for a large slice of the community. He also sees it as his duty and burden to “educate” everyone, and sees anything which uses terms in a fashion he doesn’t like as undermining his efforts to educate.

      Paul is obviously an incredibly smart guy, but the muck slinging over “oh you used the wrong term here, everyone knows what you mean but I’ll blog about it anyway” is getting ridiculous and boring. The community would be better off if both Paul and Taylor simply ignored one and other. Or got together in a pod cast and actually had it out in a medium other than hiding behind the relative safety of blogs and tweets.

      • > Yeah it’s “another” attack on Laravel

        Let’s dispense with the word “attack”. I have no animosity here. This is a technical note, pure and simple.

        > I get the feeling that Paul believes he should somehow have framework supremacy on that basis alone

        Let’s put that one to rest, too. I have no expectation of “supremacy” on that basis, or any other. The way-of-work and the tradeoffs I prefer are very different from the median (mode?) PHP developer. As such I figure my offerings will appeal to a much smaller set of developers. It is my *hope* that set of developers is in the range of +1 or +2 standard deviations, but of course one flatters oneself.

        > if all that’s wrong with the framework is the selection of a few words then Taylor must be doing something right

        Which means it should be an easy fix to make.

        • > Let’s dispense with the word “attack”.

          Let’s not. It is what it is.

          > I have no expectation of “supremacy” on that basis, or any other.

          what is the motivation then? Intellectual superiority? The smug satisfaction that you get for being “right” (from your point of view)? I don’t get it.

          If you have actual technical issues with the framework, then by all means we would LOVE to hear them. But I have yet to see any from you (if you have made technical critiques please point me in the direction of them, and I’ll humbly stand corrected).

          Seriously, how do you feel this moves the community forward? Surely there is benefit in either ignoring each other’s work or getting on a hangout and actually having a debate in a medium where responses can be had in real time and not from the comfort of sniping behind blog posts and Tweets (for the sake of balance I think Taylor is as bad as you in this sideshow.)

          • > what is the motivation then

            My hope is to help others improve, in the way others before me helped me to improve: by pointing out errors, especially what seem to me to be obvious errors, and how to correct them.

            > how do you feel this moves the community forward

            My thought is that a common vocabulary, widely understood and used properly, is an aid to communication in general. Surely that’s a good thing.

          • Paul, forgive me for doubting your sincerity, but you seem to direct your naming crusade at one product, and one person within the community. Is this because every other major PHP project has gotten all the terminology right and doesn’t deserve your attention?

            And why is it that *your* particular brand of “common vocabulary” wins? Indeed, the wikipedia entry (OK, not a necessarily reliable source) on this very topic refers to “junction” tables with a multitude of synonyms, including association table *and* pivot table. Semantic diffusion maybe, but it would seem the terminology has already passed into common usage in this case.

          • > you seem to direct your naming crusade at one product, and one person

            Until I was named by a person directly, I directed *nothing* at any person.

            > Is this because every other major PHP project has gotten all the terminology right

            When I see terms being misused in popular discourse (e.g. Reddit) then I perk up.

            > why is it that *your* particular brand of “common vocabulary” wins?

            I have no brand of vocabulary (ADR notwithstanding). Everything I have referenced comes from other, much older sources.

            > including … *and* pivot table

            “Citation needed.”

          • Really, all this boils down to you feeling like an outgroup (or out-person) has disrespected your tribe. You don’t know me well enough to understand my detachment here, aside from narrowly and specifically the items I have explicitly mentioned.

          • I have no “tribe” Paul. I’ve used many frameworks over the years, and continue to use many frameworks and libraries as the need presents itself. I’ll use whatever tool gets the job done . Often that means Laravel but it’s not because of tribal loyalty.

            I don’t see the constant intellectual dick measuring as particularly helpful though. We get it, you really like to feel that you are right. Good for you. You probably are right. Does it really matter what they are called though in the scheme of things? Probably not. Doesn’t stop me making money, doesn’t make my products suddenly stop working. Doesn’t make them any less reliable. Doesn’t even make it any harder to explain to colleagues how parts of the software works.

            And of course I don’t know you well enough. I know only the outward persona that you portray the world. A thoroughly intelligent albeit arrogant and generally unlikable fellow who lacks the social graces to carry forward meaningful discourse with those who disagree with him. Is Taylor any better in this regard? Absolutely not. Both of you need to stop being dicks, and even if you don’t like each other, at least be excellent to each other.

          • > Does it really matter what they are called though in the scheme of things? Probably not.

            Then I don’t see what the problem would be with changing them to something that is more correct.

            > if you don’t like each other, at least be excellent to each other.

            Funny, I don’t recall saying anything to indicate I don’t like him. Note who’s been doing the name-calling in all this. Again, you infer a personal animosity where none exists.

          • > Then I don’t see what the problem would be with changing them to something that is more correct.

            The same is equally true of not changing it. If it provides no benefit then why change it? If in the scheme of things it doesn’t matter, both answers are equally satisfactory, given that “being right” is entirely subjective and in this case of very little value.

            > Funny, I don’t recall saying anything to indicate I don’t like him.

            Something about actions speaking louder than words? You may like or be indifferent towards him, but it comes across that you do not like or respect him, his work, or his community. That’s the impression you are giving people. If this is not the impression you wish to be giving, perhaps there is work to do.

            > Note who’s been doing the name-calling in all this.

            “He started it Sir….” Please.

            I have claimed many times you two are as bad as each other. But tell me, if you worked incredibly hard on a thing, and received nothing but negativity and criticism from a certain portion of the community, could you not see a version of yourself that might take this level of criticism incredibly personally? Especially if the work that is being criticised is largely your own work? OK, maybe you personally have a much thicker skin, but whilst it doesn’t excuse Taylor’s recent behavior it goes some way to explaining it.

            > Again, you infer a personal animosity where none exists.

            My statement still stands. Whether there is or there isn’t personal animosity (and whether you care to admit it or not, you give off a very strong indication that you do not like, or even respect Taylor), professionally you should be excellent to each other.

          • > If it provides no benefit then why change it?

            It provides the benefit of using a common vocabulary.

            > “He started it Sir….” Please.

            I’m not saying he *started* anything. I have not called names; he has.

            > My statement still stands.

            And incorrectly so.

  3. Yea, I was surprised to find the usage of “pivot tables” and have been used to Join tables vs. Pivot tables, as Paul explained above. I wasn’t sure if this was a modern change in terminology, or some other recent advancement. I guess it shows the pitfall of “official jargon” in any industry, that when (unintentionally) misused, becomes even more confusing.

    • To avoid multiple rows showing the same one piece of info, that Article 5 contains Tag 7. With no PK in the association table, nothing prevents the same pairing to be signified by identical multiple rows. At a minimum, it’s a “tidy table” thing. I think it also makes lookup queries easier to consume because the caller does not need to prevent duplicates from resulting in something like Tag 7’s description appearing many times when Article 5 is viewed.

      • article_tag_id and article_id could constitute the PK, so the first part of your answer doesn’t make sense.

        • *Could*, but sometimes it’s easier to stay consistent and use a surrogate PK (esp with auto increment).

  4. thanks Paul, keep writing the blog posts, they are very informative and teaching.

    Greg or whatever you are called, stop your fuss and do your work. Try to not fall for the business models of some framerworks and be tolerant of opinions. Share yours in your blog post if you so desire.

    Being informative is a very honorable task. I see no attack or anything you are mentioning here.

    You want to see attacks? head to http://craftitonline.com and that too is PERMITTED and i think generates HEALTHY DISCUSSION. Communities are about discussing, not getting pissed off by people’s opinions.

    :+1: Paul good that even your enemies here recognize you are smart, he he, you are doing a great job!

  5. Good post, thank you. I read the comments from Greg and I am a little shocked by the vehemence (not utterly surprised of course, this is the Internet after all!)

    I am, I suppose, “classically trained” in computer science and when I began using Laravel the term “pivot table” confused me too, because in my understanding of the term it didn’t fit. I whole heartedly agree that in order to communicate efficiently and effectively we must agree on a glossary of terms and their definitions. It’s clear that “computer science” is far from having a fully agreed upon set of definitions (that’s for sure!) but when it’s clear one term has been incorrectly used (deliberately carelessly? Accidentally?) then it requires posts like these to clear things up. Especially if the source of the contestation hasn’t addressed it.

  6. Paul

    At my dev shop we use the term “pivot table” to refer to what you call “association table”. We’ve had multiple conversations to the effect of, “What is the best term? There’s no clear definitive source on the Internet to clarify?” (I had no dealings with these kinds of tables before working here, so I didn’t bring an opinion into the mix.)

    I stumbled upon your blog and this title really caught my eye.

    Can you provide any citation / reasoning to back up using one name over the other? In all sincerity, I’m just looking for a canonical source of reference so it’s not so subjective.

    • One easy reference is via Wikipedia …

      http://en.wikipedia.org/wiki/Pivot_table

      … which states “a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software.” Yeah, yeah, Wikipedia, I know. 😉

      Even so, the Association Table in an Association Table Mapping might be called by many terms, but some of those terms are more-correct than others, and “pivot table” is the least-correct one (and I assert it is not correct at all).

      Indeed, Google for the phrase “pivot table” and compare that to a Google for “association table.” The exercise should be instructive.

  7. Thanks, Paul for an very informative post. I also do not see any attack in what you have written here and cannot understand how people can interpret a constructive criticism of incorrect terminology as an “attack”.

    I develop with Laravel and like the framework a lot, but I do feel that its use of the term “pivot” for an association between two tables is confusing and should be changed. The term “pivot table” is well known in the context of data summarization and in this context it perfectly describes the process by which the table is created; by pivoting the data around a column to produce a summary table. If people question the wiki reference, then people need only look at the dictionary definition of “pivot”, “To cause to rotate, revolve, or turn”.

    In the context of many-to-many relationships, the term “Association table” makes a lot more sense because it describes what the table is; a table born out of the relationship between two other tables.

    So if the choice is between a term that is ambiguous and confusing and a term that is clear and unambiguous, my vote goes strongly for the use of the unambiguous term.

Leave a Reply

Your email address will not be published. Required fields are marked *