Re: Country and Region regulations model

From: Bernard Peek <>
Date: Mon, 11 Nov 2002 13:54:13 +0000
Message-ID: <>

In message <xiMy9.10561$>, neil <> writes
>"Bernard Peek" <> wrote in message
>> In message <d%8y9.7156$>, neil
>> <> writes
>> >I need to track regulations (i.e. laws) by country. Occasionally there
>> >regulations that only apply to a single region of that country (i.e. a
>> >regulation is either a country-wide one or unique to a region of that
>> >country). Related to the regulation are a whole lot of other things such
>> >multiple language translations, so it would be good not to have separate
>> >Country- and Region-Regulation tables.
>> That won't work because countries contain regions and regions contain
>> smaller regions. This looks like a candidate for an object- or
>> class-based database. If you use the RDBMS approach you need one table
>> for regions (which may be countries) with a recursive parent/child
>> relationship.
>As I have no expertise with OO databases, I'd prefer not to change the DBMS
>for now, so I must stick with an RDBMS.
>I don't see how I can maintain the integrity of this suggested mixed regions
>table. The values would come from either a Country domain or a
>Country-Regions domain (Regional names are not necessarily unique). The
>Country domain must exist independently for other relationships, although
>the Region domain is only used in this one (at least, in my application

There is only one domain. Countries and what you refer to as regions are all geographic areas with lawmaking authority. You can add a boolean attribute is_country but you need to look very closely at exactly what that attribute means and what (if anything) it might be used for.

>> Regions inherit laws from their parents.
>This isn't the way I see it in my application. Country laws are not Regional
>laws in the way I think the inheritance approach above operates. It may be
>that if one lives in a region then both Country and Regional laws are
>applicable, but this doesn't mean Country laws are also Regional laws.

Country laws are regional laws, in that they apply to a geographic region (and some or all of the child regions within the country). Each region has two sets of laws; those it inherits and those it passes in its own legislature. Either of those may be the empty set.

>Another issue would be that the top level rows (countries) of the recursive
>parent/child relationship would need a null value for its parent. That would
>then force a surrogate PK as the two columns could no longer be used as a
>multiple PK. In fact, this table would contain different entities - parents
>with children and parents with no children (or is it children with no
>parent:-) I think I want to avoid that.

Boundary conditions often need to be handled programmatically. I have seen recursive relationships that used flag values in the parent/child ID fields. So a value of "0000" indicates a top-level entry with no parents and "9999" identifies a bottom-level entry with no children.

>> These laws may be modified by
>> local laws. For instance the European Convention on Human Rights was
>> introduced across Europe but was implemented in a slightly different way
>> in each country. The national government delegates the authority to
>> charge penalties for minor offences, like littering. Local councils then
>> pass by-laws to set the level of the penalties. This is an instance of a
>> parent/child relationship between laws as well as one between geographic
>> areas.
>This is a good point. I need to check in my application domain whether this
>happens. My gut feel is that amendments to laws are done by the law-maker,
>but I could be wrong.

In this case a local by-law is a child of an enabling law passed at a higher level. You may want to track that relationship. There is a similar relationship between resolutions passed at the European level and implementations in each of the European countries.

>> Just to make life more interesting Wales and England are different
>> countries but the laws that govern them are passed by the government of
>> neither.
>Much as I love England and Wales as beautiful countries, they are regions in
>my application domain.
>Also, I won't be tracking below the Country-Region level to regions within
>regions. Where this happens, the laws are regarded as regional laws. Here, I
>am following the principle of modelling my application domain and not the

That's perfectly reasonable if and only if the system users are aware of the lower level and have made a conscious decision not to include it.

>> Aren't you glad you asked?
>> --
>> Bernard Peek
>> SF & Computing book reviews and more.....
>> In search of cognoscenti
>Yes indeed. Thanks for your time to respond to me.
>For now, I have adopted a simple (and almost certainly wrong) approach as I
>need to get the user interface working quickly. Country, Region and
>Regulation tables with a nullable FK for region in the Regulation table. It
>makes the addtion of regionality to my user interface a piece of cake.

Simple systems often work. What is important is that you understand the complete logical data structure before you begin coding. In general any deviation from the logical data structure has a cost and you have to understand what the costs are and that sometimes costs are deferred.

Bernard Peek SF & Computing book reviews and more.....

In search of cognoscenti
Received on Mon Nov 11 2002 - 14:54:13 CET

Original text of this message