On 23/06/11 23:28, Tarlika Elisabeth Schmitz wrote:
> Hello Gavin,
>
> On Wed, 22 Jun 2011 20:53:19 +1200
> Gavin Flower<GavinFlower@archidevsys.co.nz> wrote:
>
>> [...]
>> This design ensures that: names of towns are unique within a given
>> country and>region.
>> Note you will still need business logic, in a trigger or some such, to
>> ensure that only one town within a given country and region is marked
>> as the name of>the town rather than as an alias.
>>
>> [...]
>> CREATE TABLE town
>> (
>> id serial PRIMARY KEY,
>> country_region_fk integer REFERENCES country_region (id),
>> is_alias boolean DEFAULT true NOT NULL,
>> "name" character varying(50) NOT NULL,
>>
>> UNIQUE (country_region_fk, "name")
> Many thanks, also to David, Misa and Merlin for taking the time to post.
>
> The concept of having separate tables for country/region/town sprang
> from another discussion how to derive this information from freeform
> text. Therefore alias tables might contain common
> abbreviations/misspellings (which I can't detect with soundex, etc.). I
> even have a table of non-standard country codes and I'd find it messy
> to store these invalid variations in my "clean" country/region tables.
>
>
> For the time being I plumped for a solution found in a thread Alban
> Hertroys had pointed out:
> http://postgresql.1045698.n5.nabble.com/Constraint-to-ensure-value-does-NOT-exist-in-another-table-td4493651.html
>
> I created a function townname_exists (countryfk,regionfk,name), which I
> use in conjunction with a check constraint. The constraint operates on
> the alias table and the function searches the main table.
>
> The downside is that I need to mirror the logic for both tables and
> therefore need two separate functions (one checking town and one
> townalias).
>
>
I think ir is safer, and simpler, to have a flag in one table
indicating the status as reliable or not - rather than have duplicate
logic that is a potential maintenance nightmare.