Regional Tier structure - Help needed

[MaliciousMike]

Afternoon.

I'm working on a relational database for all of our clients and wish for it to be generic for the big clients and the small clients.

My main table has fields "CompanyCode", "RegionCode"

Some companies have 18 regions, others have 5 or less, some have none. For some of our clients, they have Divisions of Regions and super regions.

What i don't want to create is a bunch of ad hoc tables.

What I'm asking is the best method of creating an endless list of tiers without having an endless amount of tables. Also, this needs to be able to fit into those 2 fields (CompanyCode, RegionCode).

I'm happy to expand on my tables as long as i wont need to edit anything if a company comes on our books with like 5 tiers.

Thanks for any help you can give

 

[DCrake]

Simple Software Solutions

Assuming that you coome up with a solution I can guarantee that individual companies with none or many tiers will want to analyse their data at each level. So somehow you need to be able to structure your tables in such a way that the user can interrogate their data at any level. The heirarchy of the structure is the most important issue here. as each child level needs to be unique to its own parent level. I can't see how you can get away form handling this with just two fields.

Super Division(s)
Division(s)
Region(s)

You are going to need to add a least one more field for each level in the heirachy.

CodeMaster:

 

[twoplustwo]

Assuming that you coome up with a solution I can guarantee that individual companies with none or many tiers will want to analyse their data at each level.

This is so so true.

Before I joined my present company out of university this wasn't considered for a customer forecasting system. We have meter data (a site can have many meters) and portfolio level data. We often get requests for aggregated data at various levels i.e. customer which is a huge pain in the ass as you have to manually query the obviously huge back ends.

It will be worth the (lesser amount of) pain of constructing it as DC laid out.

 

[georgedwilkinson]

You only need 3 tables and you can have an unlimited number of levels in your hierarchy.

BoundaryTypes:
ID
BoundaryType (Text description of the boundary type)
FromDate (Date)
ToDate (Date)
Description (Text)
etc.

CompanyBoundaries:
ID (PK Autonumber)
CompanyID (FK Number from Parties table)
CompanyBoundary (Text description of an instance of a Company Boundary)
BoundaryTypeID (FK Number from BoundaryTypes table)
FromDate (Date)
ToDate (Date)
Notes (Text)
etc.

CompanyBoundaryRelationships:
ID (PK Autonumber...just in case)
FromBoundaryID (FK Number from CompanyBoundaries table)
ToBoundaryID (FK Number from CompanyBoundaries table)
FromDate (Date)
etc.

This design totally separates the table objects involved from non-related objects at an atomic level.

 

[MaliciousMike]

Cheers for the help guys.
I knew this project was going too good to be true!

The other option that i was considering is using the codes of the regions to define it's tier.

For example, if MrCompany had 6 regions, and each region had 5 divisions, then i would have 30 different codes in the RegionCode column.
Region 1 Division 1 would have the code 11
Region 3 Division 5 would have the code 35

The problem i had with this though, is that if MrCompany2 had 11 regions but no divisions, then queries and reports based on the above factor wouldn't work.

Another option i had is that I would have 1 table called Group, and in there would be Tier1 Tier2 CompanyCode RegionCode

In a divisional case, regionCode would be the Division and Tier1 would be the "super region" and tier2 would be the "Region". As the CompanyCode would be the same for all, reports could be based on a group level too just on that.

If it helps at all, we will be using Crystal Reports as our reporting system on these tables. Whether that means I need to change something, i have no idea as i have no experience with CR.

Thanks again for all the help.

 

[neileg]

Surely you can do this with one table and a self join. Each record holds the PK of it's parent element as a self join FK. For records at the top of the heirarchy, the parent element is null.

 

[georgedwilkinson]

Surely you can do this with one table and a self join.

That is correct.

 

[MaliciousMike]

I must admit I've never used self joins.
One of my biggest problems with this project is to make it idiot proof.
When i leave the company it needs to be able to be handed over to someone who may not understand SQL or Visual Basic like myself (or not, as it may seem...).