On 23/06/11 23:28, Tarlika Elisabeth Schmitz wrote:
Hello Gavin,
On Wed, 22 Jun 2011 20:53:19 +1200
Gavin Flower<GavinFlower@xxxxxxxxxxxxxxxxx> 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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
