Country and Region regulations model

From: neil <neil_at_efc.be>
Date: Wed, 06 Nov 2002 13:42:33 GMT
Message-ID: <d%8y9.7156$Nd.2976_at_afrodite.telenet-ops.be>

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.

How do I model this? Here are the ways I am considering:

1 Use a super/sub-table model with Regulation being the super and CountryRegulation/RegionRegulation being the sub-table. Like this:

CREATE TABLE Country(
CountryCode CHAR(3) NOT NULL PRIMARY KEY);

CREATE TABLE Region(
CountryCode CHAR(3) NOT NULL,
RegionCode CHAR(3) NOT NULL,
FOREIGN KEY (CountryCode) REFERENCES Country (CountryCode), PRIMARY KEY (CountryCode,RegionCode));

CREATE TABLE Regulation(
RegulationCode CHAR(15) NOT NULL PRIMARY KEY);

CREATE TABLE CountryRegulation(
RegulationCode CHAR(15) NOT NULL PRIMARY KEY, CountryCode CHAR(3) NOT NULL,
FOREIGN KEY (CountryCode) REFERENCES Country (CountryCode), FOREIGN KEY (RegulationCode) REFERENCES Regulation (RegulationCode));

CREATE TABLE RegionRegulation(
RegulationCode CHAR(15) NOT NULL PRIMARY KEY, RegionCode CHAR(3) NOT NULL,
CountryCode CHAR(3) NOT NULL,
FOREIGN KEY (RegionCode,CountryCode) REFERENCES Region
(RegionCode,CountryCode),

FOREIGN KEY (RegulationCode) REFERENCES Regulation (RegulationCode));

I think this works, but its a bit complex. Also, I need to make sure that the same RegulationCode doesn't exist in both sub-tables. Deleting things from any one of the super/sub-tables looks full of danger - referential integrity will work only from a sub-table to the super-table and not from super- to either of the sub-tables. So I could end up with a row in Regulation with no corresponding row in either of the sub-tables.

2 Change the Regions table to mean RegionsCovered and then include an value for each country that indicates all regions (e.g. ALLREGIONS)

CREATE TABLE Country(
CountryCode CHAR(3) NOT NULL PRIMARY KEY);

CREATE TABLE RegionCovered(
CountryCode CHAR(3) NOT NULL,
RegionCode CHAR(10) DEFAULT ALLREGIONS NOT NULL, FOREIGN KEY (CountryCode) REFERENCES Country (CountryCode), PRIMARY KEY (CountryCode,RegionCode));

CREATE TABLE Regulation(
RegulationCode CHAR(15) NOT NULL PRIMARY KEY, CountryCode CHAR(3) NOT NULL,
RegionCode CHAR(10) NOT NULL,
FOREIGN KEY (CountryCode,RegionCode) REFERENCES RegionCovered
(CountryCode,RegionCode));

I think this will work too, but I wonder what I have compromised? It means there must always be a value of ALLREGIONS in RegionsCovered for every Country. It also means that I can't use RegionsCovered for other regional information as the RegionCode domain is now RegionCode: "ALLREGIONS", Char(3), as ALLREGIONS is not actually a Region.

I could also make the Regulation.RegionCode nullable - meaning the regulation covers all regions. (Or does it mean no regions)

So, how best do I model this set of relations? Are there alternative and better solutions? Is there any other information needed before a decision can be made? Any comments are gratefully received.

Neil Received on Wed Nov 06 2002 - 14:42:33 CET