Design: How to avoid violation of 2NF/splitting of compound primary key?

SQL Apprentice Question
I'm designing a new locations model.

I need to support the standard locations structures (country,
state/province, county/parish, etc.) and several code standards but
also need to support known and to-be-user-defined regions.


Since country, state, county and even city are technically "regions" --
composed of zero or more other regions, I have subtyped a Region table
(which I call Location for political reasons) to support/disambiguate
all these (Location)Types.


The question is whether to include the LocationType in the primary key
of the Location table.


Pro: The member location will be easily identified/selected from the
subtype table
Con: The parent locatioon (e.g. State)'s Location Type will be the
same for the entire table, thus violating 2NF... redundancy. Or am I
wrong?


DDL follows (sorry for the length, but I find it impossible to help
other people when I don't have the model in front of me, and I'm
guessing I'm not the only one).


Also, I'd love to hear constructive criticism. Designing is a fine
balance of clarity and performance, and since location data is not
strictly foreseeably hierarchical but pretty darn static, I'm hoping to
bank on indexed views as the best way for developers to reference the
data in their sprocs.


TIA


CREATE TABLE Catalog
(
Catalog_id integer IDENTITY (1,1) ,
Catalog_nm dm_Name ,
Catalog_desc dm_Description
)
go


ALTER TABLE Catalog
ADD PRIMARY KEY (Catalog_id ASC)
go


CREATE TABLE CityTown
(
StateProvince_Location_id integer NOT NULL ,
Country_Location_id integer NOT NULL ,
CityTown_Location_id integer NOT NULL
)
go


ALTER TABLE CityTown
ADD PRIMARY KEY (StateProvince_Location_id ASC,Country_Location_id
ASC,CityTown_Location_id ASC)
go


CREATE TABLE CityTownMember
(
CityTownMember_seq dm_Sequence ,
StateProvince_Location_id integer NOT NULL ,
Country_Location_id integer NOT NULL ,
CityTown_Location_id integer NOT NULL ,
CityTownMember_Location_id integer NOT NULL
)
go


ALTER TABLE CityTownMember
ADD PRIMARY KEY (StateProvince_Location_id ASC,Country_Location_id
ASC,CityTown_Location_id ASC,CityTownMember_Location_id ASC)
go


CREATE TABLE CodeStandard
(
CodeStandard_id integer IDENTITY (1,1) ,
CodeStandard_nm dm_Name ,
CodeStandard_desc dm_Description ,
Catalog_id integer NULL
)
go


ALTER TABLE CodeStandard
ADD PRIMARY KEY (CodeStandard_id ASC)
go


CREATE TABLE CountryMember
(
CountryMember_seq dm_Sequence ,
Location_id integer NOT NULL ,
CountryMember_id integer NOT NULL
)
go


ALTER TABLE CountryMember
ADD PRIMARY KEY (Location_id ASC,CountryMember_id ASC)
go


CREATE TABLE CountyParish
(
StateProvince_Location_id integer NOT NULL ,
Country_Location_id integer NOT NULL ,
CountyParish_Location_id integer NOT NULL
)
go


ALTER TABLE CountyParish
ADD PRIMARY KEY (StateProvince_Location_id ASC,Country_Location_id
ASC,CountyParish_Location_id ASC)
go


CREATE TABLE CountyParishMember
(
CountyParishMember_seq dm_Sequence ,
StateProvince_Location_id integer NOT NULL ,
Country_Location_id integer NOT NULL ,
CountyParish_Location_id integer NOT NULL ,
CountyParishMember_Location_id integer NOT NULL
)
go


ALTER TABLE CountyParishMember
ADD PRIMARY KEY (StateProvince_Location_id ASC,Country_Location_id
ASC,CountyParish_Location_id ASC,CountyParishMember_Location_id ASC)
go


CREATE TABLE Location
(
Location_nm dm_Name ,
Location_id integer IDENTITY (1,1) ,
LocationType_id integer NULL
)
go


ALTER TABLE Location
ADD PRIMARY KEY (Location_id ASC)
go


CREATE TABLE LocationCode
(
LocationCode_cd char(18) NULL ,
Location_id integer NOT NULL ,
CodeStandard_id integer NULL
)
go


ALTER TABLE LocationCode
ADD PRIMARY KEY (Location_id ASC)
go


CREATE TABLE LocationType
(
LocationType_id integer IDENTITY (1,1) ,
LocationType_nm dm_Name
)
go


ALTER TABLE LocationType
ADD PRIMARY KEY (LocationType_id ASC)
go


CREATE TABLE RegionMember
(
RegionMember_seq dm_Sequence ,
Location_id integer NOT NULL ,
RegionMember_id integer NOT NULL
)
go


ALTER TABLE RegionMember
ADD PRIMARY KEY (Location_id ASC,RegionMember_id ASC)
go


CREATE TABLE StateProvince
(
StateProvince_seq dm_Sequence ,
StateProvince_Location_id integer NOT NULL ,
Country_Location_id integer NOT NULL
)
go


ALTER TABLE StateProvince
ADD PRIMARY KEY (StateProvince_Location_id ASC,Country_Location_id
ASC)
go


CREATE TABLE StateProvinceMember
(
StateProvinceMember_seq dm_Sequence ,
Country_Location_id integer NOT NULL ,
StateProvinceMember_Location_id integer NOT NULL ,
StateProvince_Location_id integer NOT NULL
)
go


ALTER TABLE StateProvinceMember
ADD PRIMARY KEY (StateProvince_Location_id ASC,Country_Location_id
ASC,StateProvinceMember_Location_id ASC)
go


ALTER TABLE CityTown
ADD FOREIGN KEY (CityTown_Location_id) REFERENCES
Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE CityTown
ADD FOREIGN KEY (StateProvince_Location_id,Country_Location_id)
REFERENCES StateProvince(StateProvince_Location_id,Country_Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE CityTownMember
ADD FOREIGN KEY
(StateProvince_Location_id,Country_Location_id,CityTown_Location_id)
REFERENCES
CityTown(StateProvince_Location_id,Country_Location_id,CityTown_Location_id­)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE CityTownMember
ADD FOREIGN KEY (CityTownMember_Location_id) REFERENCES
Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE CodeStandard
ADD FOREIGN KEY (Catalog_id) REFERENCES Catalog(Catalog_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE CountryMember
ADD FOREIGN KEY (CountryMember_id) REFERENCES Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE CountryMember
ADD FOREIGN KEY (Location_id) REFERENCES Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE CountyParish
ADD FOREIGN KEY (CountyParish_Location_id) REFERENCES
Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE CountyParish
ADD FOREIGN KEY (StateProvince_Location_id,Country_Location_id)
REFERENCES StateProvince(StateProvince_Location_id,Country_Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE CountyParishMember
ADD FOREIGN KEY
(StateProvince_Location_id,Country_Location_id,CountyParish_Location_id)
REFERENCES
CountyParish(StateProvince_Location_id,Country_Location_id,CountyParish_Loc­ation_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE CountyParishMember
ADD FOREIGN KEY (CountyParishMember_Location_id) REFERENCES
Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE Location
ADD FOREIGN KEY (LocationType_id) REFERENCES
LocationType(LocationType_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE LocationCode
ADD FOREIGN KEY (Location_id) REFERENCES Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE LocationCode
ADD FOREIGN KEY (CodeStandard_id) REFERENCES
CodeStandard(CodeStandard_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE RegionMember
ADD FOREIGN KEY (RegionMember_id) REFERENCES Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE RegionMember
ADD FOREIGN KEY (Location_id) REFERENCES Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE StateProvince
ADD FOREIGN KEY (Country_Location_id) REFERENCES
Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE StateProvince
ADD FOREIGN KEY (StateProvince_Location_id) REFERENCES
Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE StateProvinceMember
ADD FOREIGN KEY (StateProvince_Location_id,Country_Location_id)
REFERENCES StateProvince(StateProvince_Location_id,Country_Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


ALTER TABLE StateProvinceMember
ADD FOREIGN KEY (StateProvinceMember_Location_id) REFERENCES
Location(Location_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go



Celko Answers
>> I'm designing a new locations model. I need to support the standard locations structures (country, state/province, county/parish, etc.) and several code standards but also need to support known and to-be-user-defined regions. <<


Thank you for the DDL. The flaws I see are the use of IDENTITY as a
key in violation of the most basic RDBMS definitions and inconsistent
naming rules for data elements. .

I would suggest a nested sets model for this. One table for locations
with a name as a key and (longitude, latitude) or whatever as an
attribute. Then one table for each geographical partitioning of the
set of locations. You would have a political divisions table, a wine
of regions table, etc, that reference the locations. The political
name of a division is an attribute.


You made it complex by having a table for every level in a hierarchy.
Model the hierarchies instead.