Re: "REFERENCES" and UNIQUE

On Tue, 17 Jan 2006, Michelle Konzack wrote:

> Am 2006-01-04 12:08:30, schrieb Stephan Szabo:
> >
> > On Wed, 4 Jan 2006, Michelle Konzack wrote:
>
> > > | CREATE TABLE countries (
> > > |     serno       int             NOT NULL UNIQUE,
> > > |     isocode     varchar(2)      NOT NULL UNIQUE,
> > > |     EN          text            NOT NULL,
> > > |     DE          text            NOT NULL,
> > > |     FR          text            NOT NULL
> > > |     );
>
> > > | CREATE TABLE cities (
> > > |     serno       int             NOT NULL UNIQUE,
> > > |     country     varchar(2)      NOT NULL REFERENCES countries (isocode),
> > > |     EN          varchar(30)     NOT NULL,
> > > |     DE          varchar(30)     NOT NULL,
> > > |     FR          varchar(30)     NOT NULL
> > > |     );
>
> > Which in this case hints that there's a problem with the schema.
> >
> > What is the final effect you're looking for?  The above does let you
> > lookup the DE or FR strings for a city of a member (what if two cities had
> > the same EN name but different DE or FR names, how would you decide which
> > one it was). If you only wanted to make sure that the city name was in a
> > list, then it wouldn't matter if you made it unique.
>
> Hmmm, never seen this.  I have checked my table and I have more
> then 30.000 cities and no doubles.

It really depends on what an entry in cities is meant to mean.  There are
many duplicate city names in the US which theoretically are different
cities but could probably potentially be the same entry if all the other
data is the same.

With a little searching, I found a Paris, Tennessee for example.

> What do you think, should I do?

This depends on the business rules, but here are some possibilities.

If it's assumed that all cities in the same country are the same row (ie,
they all have the same strings and other data associated), then
potentially the EN and country are as a pair unique and can be referenced.

If it's assumed that all cities with a given name have the same associated
data, then EN is unique and can be referenced.  Note, from the above I
would assume this is not true if cities references a country.

The safest would be something like the below, however, alot of people may
not know a useful geographical coordinate (for example long/lat) for where
they actually are.

> First I was thinking, I use additonaly geographical coordinates,
> but because I have none I have leaved them out.  Maybe I should
> use it even if I do not know it currently?
>
> > The easiest is to write triggers that check for a matching row on insert
> > or update to members and make sure that you aren't removing the last match
> > on update or deletion of cities. You need to be a little careful to get
> > the locking right for concurrent actions on cities and members.
>
> Hmmm...
>
> I think, I will change this part.
>
> If I enter new members and I add the city, I will do following
>
>     1)  enter ISO countrycode
>     2)  klick in a link to select the first letter of the city
>         which triger a query on the availlabele cities.
>     3)  now     a)  select city from the table
>            or   b)  enter a new cityname
>     4)  while submiting the new/changed member data I use only
>         the "serno" o determine which city I have choosen if
>         several cities of the same name exist
>
> This mean, I should change the TABLE to
>
> CREATE TABLE cities (
>     serno       int             NOT NULL UNIQUE,
>     country     varchar(2)      NOT NULL REFERENCES countries (isocode),
>     geo         ???,
>     EN          varchar(30)     NOT NULL,
>     DE          varchar(30),
>     FR          varchar(30)
>     );
>
> Is there a data type for geographical data like GIS or something similar?
>
> Please note, that I am using PostgreSQL 7.4.5
>
> Greetings
>     Michelle Konzack
>     Systemadministrator
>     Tamay Dogan Network
>     Debian GNU/Linux Consultant
>
>
> --
> Linux-User #280138 with the Linux Counter, http://counter.li.org/
> ##################### Debian GNU/Linux Consultant #####################
> Michelle Konzack   Apt. 917                  ICQ #328449886
>                    50, rue de Soultz         MSM LinuxMichi
> 0033/3/88452356    67100 Strasbourg/France   IRC #Debian (irc.icq.com)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>