Re: [Gnumed-devel] Questions re database schema:street:address:urb:country

[sjtan]

> > / > 1) NOMENCLATURE in table generating queries:/
> > / >/
> > / >         -Given that one will be doing various joins (and I'm not sure if/
> > / > 8.0 supports outer), is there a reason not to name the ID's and feids/
> > / >  better:/
> > / >/
> > / > ===================================/
> > / > CREATE TABLE public.country/
> > / > (/
> > / >   id serial NOT NULL,  (eg id_country serial NOT NULL)/
> > / >   code char(2) NOT NULL, (eg country_code..../
> > / >   name text NOT NULL,     (country_name)/
> > / >   deprecated date,               (country_deprecated)/
> > / >/
> > / > CREATE TABLE public.country/
> > / > (/
> > / >   id_country serial NOT NULL,/
> > / >   country_code char(2) NOT NULL,/
> > / >   country_name text NOT NULL,   /
> > / >   country_deprecated date,..... etc/
> > / /
> > / No, because in the join you have to explicitly name all duplicate columns 
> > like/
> > / select country.id, urb.id from country, urb .../
> > / /
> > / The way yu would name it would notonly quickly exhaust the 32 byte variable /
> > / length in some cases and cause finger cramps in poor programmers, it would /
> > / also take a lot of reusability out of code (like a code weeding out all /
> > / "deprecated" rows of a table)/
> Agree.
>
> Karsten
I always used to name every primary key field in any table , "id",
but the select.. natural join ..on ..  mechanism suggests
it's better to have a unique id name for each entity, and to have 
foreign keys in other tables identically named.