[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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

From: J Busser
Subject: Re: [Gnumed-devel] Questions re database schema - normalization
Date: Fri, 3 Sep 2004 00:34:20 -0700

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.

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).

reply via email to

[Prev in Thread] Current Thread [Next in Thread]