Messy One to Many Joins and Grouping

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

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

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