Messy One to Many Joins and Grouping

[Newsgroup_User]

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

[paross1]

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

[Newsgroup_User]

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
>
>

[paross1]

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

[Newsgroup_User]

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

[paross1]

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