Re: [Gnumed-devel] Questions re database schema - normalization

[Horst Herb]

On Fri, 3 Sep 2004 01:44, J Busser wrote:
> Does this mean that
>     if a join should have to operate on 2 tables
>     selecting from each a field named "id"
> that the syntax will stumble, and/or it is unwanted/extra work to
> have to provide aliases so as to be able to distinguish the column
> names and data from the two "id" fields in the output?

create table a(id serial primary key, text text);
create table b(id serial primary key, id_a integer references a(id), text 
text);

select a.text, b.text from a left join b on (a.id=b.id);

That works. Where's the problem? Whenever there is a identifier clash, all you 
have to do is explicitly name the table (unless you use aliases)

Regarding the id vs fk debate:

I recommend that 
- any primary key of a table is named "id"
- any reference to a foreign key is named id_<tablename>

Horst