5 Replies Latest reply on May 19, 2008 12:04 PM by paross1

    Messy One to Many Joins and Grouping

    Level 7
      I manage websites for a large state agency in Texas. I have a need to redo
      queries that list the Local intake numbers for Long term support services
      (LTSS), Area Agencies on Aging (AAA), and Mental Retardation Authorities
      (MRA) by county for each of the state's 254 counties -- in the past there
      was only one intake number per county for each AAA and MRA, with there being
      the possibility for multiple LTSS intake numbers. Now there are counties
      that have multiple numbers for MRA intake.

      Basic Structure of Database Tables:

      CountyCenter Table
      County (text)
      CountyNumber (integer)
      AAA_ID (integer)
      MRA_ID (integer)

      LTSS Table
      County (text)
      CountyNumber (integer)
      City (text) -- optional used when there are multiple offices for a county
      IntakeNumber (text)
      Region (text)

      AAA Table
      AAA_ID (integer)
      AAAName (text)
      AAAPhone (text)

      MRA Table
      MRA_ID (integer)
      MRAName (text)
      MRAPhone

      MRAOffices
      MRA_ID (integer)
      IntakePhone (text)
      City (Text)
      CountyNumber (integer)

      My goal is for each county to list in a combined table:

      Column 1 = County Name
      Column 2 = LTSS Office (Number(s))
      Column 3 = MRA Intake Number
      Column 4 = AAA Intake Number(s)/City if not main

      Do I need a separate table for county that simply has the CountyNumber and
      CountyName to use as a control table?

      I'd like to do this as efficiently as possible -- thankfully the query won't
      be accessed by the general public and will be run only when updated
      information is received. Any suggestions as to order of the joins and
      groupings in CFOUTPUT? (what I have now creates a lot of duplication)

      Thanks in advance for your help,

      Michael Brown
      Webmaster, Texas Department of Aging and Disability Services
        • 1. Re: Messy One to Many Joins and Grouping
          paross1 Level 2
          After taking a quick glance, it looks like you already have normalization issues with your data model, since LTSS Table and CountyCenter Table both contain County (text), and MRAOffices and LTSS Table both have City (text), etc. It is hard to tell which fields are primary keys and which are foreign keys to which tables. Some of these that may have a one-to-many relationship that now changes to a many-to-many will require a link table (associative entity) and the foreign keys migrated to them.

          You need to resist creating actual "combined tables", spreadsheet style, and instead normalize your database, so that you can create your "combined table" virtually using SQL. It is hard to offer specifics, since I would need more information to do so, but the bottom line is that you do have some obvious data model issues that need to be resolved before you can get much further. Otherwise, you are going to have to write some very kludgey SQL to solve your problem with your current model.

          Phil
          • 2. Re: Messy One to Many Joins and Grouping
            Level 7
            Phil,

            Thanks for taking a look. You're definitely right. The data
            structures are a mess. What was originally intended to be on three
            separate pages has been requested to be "available at a glance."

            With both the LTSS Offices and MRAs having the possiblity of
            multiple offices for a given county what would be the best way to
            normalize the data? The tables for MRA, AAA, and LTSS have
            CountyNumber (county exists only in those tables to provide
            context for those who update the information manually (not my
            choice/decision)).

            So to break it down 254 Counties, each can have one or multiple
            LTSS numbers (and the number's city), only one AAA number, and one
            MRA with the possiblity for multiple intake numbers (and note
            containing location information).

            Hopefully, there is a graceful way of getting the output the way
            it has been requested. I'm open to suggestions.

            Thanks again,

            Michael


            "paross1" <webforumsuser@macromedia.com> wrote in
            news:g0s4n7$eou$1@forums.macromedia.com:

            > After taking a quick glance, it looks like you already have
            > normalization issues with your data model, since LTSS Table and
            > CountyCenter Table both contain County (text), and MRAOffices
            and LTSS
            > Table both have City (text), etc. It is hard to tell which
            fields are
            > primary keys and which are foreign keys to which tables. Some of
            these
            > that may have a one-to-many relationship that now changes to a
            > many-to-many will require a link table (associative entity) and
            the
            > foreign keys migrated to them.
            >
            > You need to resist creating actual "combined tables",
            spreadsheet
            > style, and
            > instead normalize your database, so that you can create your
            "combined
            > table" virtually using SQL. It is hard to offer specifics, since
            I
            > would need more information to do so, but the bottom line is
            that you
            > do have some obvious data model issues that need to be resolved
            before
            > you can get much further. Otherwise, you are going to have to
            write
            > some very kludgey SQL to solve your problem with your current
            model.
            >
            > Phil
            >
            >

            • 3. Re: Messy One to Many Joins and Grouping
              paross1 Level 2
              First, can you supply the following information:

              1. What is the designated primary key for each table?

              2. How does each entity (table) relate to the others? (FK-->PK, one-to many, etc.)

              3. Which columns pertain directly to the table that they are in (relate directly to the primary key) and which are "related" to the other table(s)?

              Phil
              • 4. Re: Messy One to Many Joins and Grouping
                Level 7
                Phil,

                I forgot to mention that we're currently having to use Access for a
                datasource. I also used more generalized names below.


                Tables and Primary Keys
                County = CountyNumber (254 Records)
                LTSS = Office_ID (291 Records)
                AAA = AAA_ID (28 Records)
                MRA = MRA_ID (39 Records -- four of which have related records in the
                MRASatelites table)
                MRASatelites = Sat_ID (35 Records) These are related to the MRA by MRA_ID

                Foreign Keys (I hope I have the proper understanding of one)
                LTSS = CountyNumber
                AAA = CountyNumber
                MRA = CountyNumber
                MRASatelites = MRA_ID

                This worked ok until the one-to-many relationship was added for the MRAs

                Thanks again for your insights,

                Michael
                • 5. Re: Messy One to Many Joins and Grouping
                  paross1 Level 2
                  The minimum "fix" would be for you to "migrate" the MRA_ID field out of the CountyCenter table, and instead add a CountyNumber foreign key to the MRA table so that multiple MRA rows can be related to one CountyCenter. This makes it a one-to-many. However, if you can have more than one CountyCenter related to more than one MRA, then this becomes a many-to-many relationship that would need an associative entity (link table) to resolve. In that case, neither table would contain a foreign key to the other, but you would have an intermdediate table that would contain the FK to each. In other words, you would have something like a CountyCenterMRA table that would contain CountyNumber and MRA_ID pairs where these two tables are linked.

                  I'm not sure how either of these changes will affect MRASatelites except to note that you would still have a double (redundant) link between MRASatelites and CountyCenter because it also directly links to CountyCenter by its relationship to MRA (since it is already linked because of the relationship between MRA and MRASatelites and between MRA and CountyCenter. This is another normalization problem.).

                  Phil