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.
What do you think, should I do?
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)
