Modeling Question: Country | Region | Sub-Region

[(PeteCresswell)]

We've got tlkpCounty.

Securities have countries (for simplicity... actually securities
have issuers, issuers have company groups and company groups have
countries)

User wanted to be able to report by "Region", so I added
tlkpCountry.RegionID and tlkpRegion.

Now, for instance, country Germany can be in region "Europe".

Works a-ok and seems tb the good-right-and-holy path design-wise.


But now they've decided that sometimes they want to report on
various sub-regions (or sub-categories) within a region.

For instance a report on "Europe" might be broken into "Nordic"
countries, "Mediterranean" countries, and so-forth.

My knee-jerk reaction is to add tlkpCountry.SubRegionID and
tlkpSubregion.

When the user is in "Admin" mode, assigning regions and sub
regions, the country row would have a combo box for "Region" and
a text box for sub-region.

To change region, they would drop the combo box and select.

To change sub region, they would click on the sub region text box
and I'd pop a little dialog with a drop down populated with the
allowable sub regions for the currently-chosen region.


Somehow this sounds kludgy to me.

Can anybody comment on both:

- The data model

- The admin screen approach

 

[a a r o n . k e m p f]

SQL Server Analysis Services supports ragged hierarchies.

Jet has not gotten any new features in a decade.

Do the math, come to your own conclusions.
For -ME- the answer is not to use crippled database

 

[Tom]

At the risk of overcomplicating the whole thing - what happens next
week when users want to add another way to group countries... maybe by
language.

I'd consider making a table of associations wherein each country could
be associated with different groupings. So Germany might be
associated with Europe, Western Europe, Northern Europe, Germanic
Language, etc, etc... So:

tblAssociation
Country
Association

You'd then need a table for all your valid options for Association.

You might also consider adding the ability to "group" associations...
for example flag all the language associations, political
associations, and geographic associations so you can get a report that
includes all of the individual associations without the user having to
select multiple items when reporting.

The general user then doesn't need to worry about what the various
associations are... just pick the right country. The admin would need
to deal with setting up and maintaining all the various associations,
but that form should be pretty simple.



HTH

Tom

 

[a a r o n . k e m p f]

keep your logic in cubes, and it would be easy to drag and drop
multiple unrelated fields into a pivotTable to see the results you
wanted

by embracing real database servers- all your complications go away