Re: Country and Region regulations model

"Bernard Peek" <bap_at_shrdlu.com> wrote in message news:<dBVWL4cnEny9Ewvp_at_shrdlu.co.uk>...
> In message <d%8y9.7156$Nd.2976_at_afrodite.telenet-ops.be>, neil
> <neil_at_efc.be> writes
> >I need to track regulations (i.e. laws) by country. Occasionally there
are
> >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
as
> >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 domain).

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

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.

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

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

> Aren't you glad you asked?
>
>
> --
> Bernard Peek
> bap_at_shrdlu.com
> www.diversebooks.com: 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.

The downside is that I seem unable to establish referential integrity with anything other than the Country table.

Here is what I am currently using. The problem is at the end where I can't make CountryCode,RegionCode a FK:

CREATE TABLE Country(
CountryCode VARCHAR(2) PRIMARY KEY);

CREATE TABLE Region(
RegionCode VARCHAR(3),
CountryCode VARCHAR(2),
FOREIGN KEY (CountryCode) REFERENCES Country (CountryCode), PRIMARY KEY (RegionCode,CountryCode));

CREATE TABLE Regulation(
RegulationCode VARCHAR(15) PRIMARY KEY,
RegionCode VARCHAR(3),
CountryCode VARCHAR(2) NOT NULL,
FOREIGN KEY (CountryCode) REFERENCES Country (CountryCode)); //FOREIGN KEY (CountryCode,RegionCode) REFERENCES Region(CountryCode,RegionCode)); This won't work, so I am leaving it out.

I am toying with idea of making a 1:1 RegulationRegion table. This seems like a better approach for maintaining integrity through the design, but it will slow down the production of a user interface as I don't know how to code it yet.

I guess for now I need to live with the possibility that someone could add a non-existent region to the Regulation table and that region deletion and update anomalies could occur in the Regulation table.

Neil Received on Fri Nov 08 2002 - 11:25:33 CET