Problem with write query

Question

Hi, I've got collection of geo objects in database:

There are four Tables:

Countries
Regions
Provinces
Cities

Cities has inter alia ProvinceCode Provinces has inter alia regionCode Regions has inter alia CountryCode

And there is fifth Table: Descriptions

ObjectCode
ObjectType(country, region, province, city)
Description.

How to get from Descriptions table, all descriptions from objects which are in the definite country ??

Answer 1

I can see my DBA cringing if he read this... If re-factoring/normalising is an option I would either

a) Put the description column on each table... OR b) Create separate tables for descriptions for each Object... i.e.

Countries CountryDescriptions Regions RegionDescriptions etc...

If that's not an option

It might be easier to manage if you created some views to wrap them up.

CREATE VIEW vCountryDescriptions
AS 
SELECT * FROM Countries c 
JOIN Descriptions d ON c.ObjectCode = d.ObjectCode 
AND d.ObjectType = 'Country'

Repeat for each entity.

And then Join your 4 views together.

Answer 2

I suspect @zerkms won't be the only one who doesn't understand what is wrong with this data model so it is worthwhile examining its shortcomings.

1. It has no relational integrity

The primary key of Descriptions is presumably (ObjectCode,ObjectType). This doesn't map to any parent key, so there is no way to enforce a rule that a Description must belong to some object. Even if ObjectCode is unique across all tables (say it is a generated UUID) so that the primary key of Descriptions could be (ObjectCode) we would still not be able to enforce a foreign key constraint because one child key cannot reference multiple parent keys.

Conversely we also cannot enforce the rule that a Country must have a Description because foreign keys don't work that way.

@EoinCampbell's suggestion of putting the model into sixth normal form - Countries CountryDescriptions Regions RegionDescriptions etc - at least has the virtue of supporting data integrity.

2. The performance will suffer

Every query to retrieve a single set of data is now a join. Joining isn't automatically a bad thing - it's what RDBMS products are meant to do - but now we have a small table Countries joining to a much larger table Descriptions. Consequentlty, a query to retrieve all the countries and their descriptions will be a lot less efficient because it will need to winnow all the Descriptions of the other types.

Again 6NF has a benefit as it will scale better than the posted implementation.

3. Way too many tables

New requirement: we need to hold ABBREVIATION for all these objects. That is not an attribute of Description so we can't store it on that table. But we cannot put a Population column on Countries, Regions, etc because that would be inconsistent. So we need another table Abbreviations. Oh and the users would like to hold POPULATION as well. And AREA if you don't mind. Before you know it, select * from countries has become a five-table join.

This is where 6NF breaks down. The number of tables required rapidly metastasizes into a schema of mind-boggling proportions.

Which is why most sensible people stop at BCNF, or at least 3NF.

Answer 3

Putting away the soap box, here is an actual solution:

select Countries.code as  country_code  
       , count_d.description as country_desc
       , Regions.code as  region_code  
       , reg_d.description as region_desc
       , Provinces.code as  province_code  
       , prov_d.description as province_desc
       , Cities.code as  city_code  
       , city_d.description as city_desc
from  Countries
        join Descriptions count_d
             on ( count_d.ObjectCode  = Countries.code
                  and count_d.ObjectType = 'COUNTRY' )
        join Regions
             on ( Regions.CountryCode = Countries.code )
        join Descriptions reg_d
             on ( region_d.ObjectCode  = Regions.code
                  and count_d.ObjectType = 'REGION' )
        join Provinces
             on ( Provinces.RegionCode  = Regions.code )
        join Descriptions prov_d
             on ( prov_d.ObjectCode  = Provinces.code
                  and count_d.ObjectType = 'PROVINCE' )
        join Cities
             on ( Cities.ProvinceCode  = Provinces.code )
        join Descriptions city_d
             on ( city_d.ObjectCode  = Cities.code
                  and count_d.ObjectType = 'CITY' )
where Countries.whatever = 'DONDESTAHN'
/

Not actually tested, so watch out for typos! They're a particular danger with Cut'N'Paste Driven Development.