5 Replies Latest reply on Nov 3, 2015 1:57 PM by PeterDoern

    Need help with a Join Table question

    margoth

      Background: My database has a People table and a Purchases table, which we use to track purchases by individuals (a one-to-many relationship, linked by a PPLID foreign key in the Purchases table). A co-worker wants to be able to start tracking purchases made by organizations. We actually already have an Organizations table, with an OrgID primary key.

       

      So my (rookie!) question is this: if I make a new field in the Purchases table for the user to select a foreign key OrgID and link the tables, would that enable a purchase to be assigned to either an individual or an organization, basically turning the Purchases into a Join Table? This seems like a better option than creating a separate OrgPurchases table, since all the other transactional info would be the same...

       

      Thanks in advance for any feedback or alternative suggestions!

        • 1. Re: Need help with a Join Table question
          siplus

          Do People belong to organizations, or do products purchased belong to organizations (producing them) ? It's not clear, even if I think it's the former one.

          • 2. Re: Need help with a Join Table question
            PeterDoern

            I'm not sure the term Join Table applies here. You'd use a Join Table to create a many-to-many relationship between two entities. For example, if a person could belong to many organizations (like the Lions and the Rotary), and an organization can have multiple people, then you'd want to make a Join Table called, say, Members which would have a foreign key for the Person and a foreign key for the Organization.

             

            In this case, however, what we're talking about is a common table, Purchases. Like you say, a purchase can be made either by a Person or an Organization. The easiest solution is to have a foreign key field for Person and a foreign key field for Organization, with the intent being that only one of those would be populated at a time.

             

            And yes, for reporting purposes, e.g. getting a total of all purchases regardless of whether it's a person or an organization, you'd keep Purchases in one table.

            1 of 1 people found this helpful
            • 3. Re: Need help with a Join Table question
              margoth

              Thanks, Peter- your response is very helpful. I think I was tripping over the unfamiliar terminology- your description of the 'common table' sounds like what I'm looking for. I'll give it a try, and hopefully I won't break anything!

              • 4. Re: Need help with a Join Table question
                margoth

                Thanks, siplus. We're publishing a magazine that can be purchased by individual People, or by Organizations. Our database is setup such that an Organization can have many People associated with it. Peter's 'common table' is essentially what I'm going for- letting the user attribute a purchase to either a Person or an Organization.

                • 5. Re: Need help with a Join Table question
                  PeterDoern

                  No problem, Margoth, glad I could help.