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

[J Busser]

At 6:04 PM +1000 9/3/04, Horst Herb wrote:
> Regarding the id vs fk debate:
>
> I recommend that
> - any primary key of a table is named "id"

(except pk_audit must be named that way for the postgres generator to 
function??)

> - any reference to a foreign key is named id_<tablename>


At 8:31 AM +1000 9/3/04, Richard Terry wrote:
> when you are using say a visual designer where you can drag and
> drop the joins (like pgaccess) then you can't tell what foreign key is likely
> (meant to) be linked to what table!


Under Horst's proposal, id_<tablename> can be understood to be linked 
to the field "id" in the table <tablename>. But when linking a 
table's primary key ("id") to the corresponding link field in another 
table ("id_originalTableName) our brains could make us want to match 
instead "id" to "id" and all the worse if the visual designer 
suggests it. The problem could (merely?) require some discipline to 
avoid.

If instead primary keys were named "id_<tablename>" then any table 
that contains a primary key and one or more foreign keys will have 
multiple fields of the form id_* i.e.
id_<tablename>
id_<someOtherTableName>
id_<yetSomeOtherTableName>

You have to keep the current table name in mind before you can 
identify which is its primary key. If the convention *were* to become 
id_<tablename> for *primary* keys, then I would suggest in place of 
"id_" to use "fk_" for foreign key references -- which it appears 
that Karsten has already often used.

Karsten commented in the table clin-root_item
re "pk_item"
> the primary key, not named "id" as usual since child tables  will 
> have "id" primary keys already

I am not sure why child tables already having their own primary "id" 
keys means why "id" should not be used for the primary key in 
clin_root_item. The foreign key fields in the child tables are named 
fk_item, can they not reference a field "id" in clin_rot_item?

Suggest in the other tables, changing "fk_item" to "fk_cri" or 
"fk_cr_item" (to more clearly identify the table as "item" is not the 
table name).