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

[Karsten Hilbert]

> >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??)
Nope. Because of inheritance:

create table root (
    id int
);

create table leaf (
    id int
) inherits (root);

Here leaf would end up with two id columns.

Hence pk_audit and pk_item.

> 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.
Hence I name foreign keys:

fk_*

I used to name them fk_fieldname_tablename, eg fk_pk_address
but that got unwieldy so that now I operate under the
assumption that I will nearly always be linking to the primary
key of the other table. IOW fk_address means "a foreign key to
the primary key of address".

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

> 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
comment improved

> 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.
See above: inheritance.

> 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).
Not sure which fk_item you are referring to ? Any descendant
table of clin_root_item *already has* the pk_item virtue of
inheriting it ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346