[Koha-devel] [DISCUSSION] Database schema & coding guidelines

[Marc Balmer]

Am 25.05.12 18:00, schrieb Paul Poulain:
> In the wiki page http://wiki.koha-community.org/wiki/DB_schema_bugs, an
> insconsistency has been noticed:
> primary keys column name can be: id (in authorised_values table), xxxid
> (cityid), xxx_id (label_id), xxxId (limitId), xxxcode (branchcode),
> xxxnumber (borrowernumber) => choose how to name PK and update schema
> accordingly

Technically, the name of a column does not matter much, since you can
always refer to it as <tablename>.<columnname> to make it unambigous.
In some databases that support schemas, like PostgreSQL, you can refer
to columns even as <schemaname>.<tablename>.<columnname>.

> 
> The bug 7065 add a foreign key to the reserves table, so I think it's
> time to clearly choose how we name primary keys.
> 
> The possible options are below, my opinion:
> 
>  1- id alone => will result in complexity & mistakes, this option must
> be discarded

I contradict.  This results neither in complexity nor in mistakes.
Imagine two tables, A and B, which both have an id column, then a select
would probably look like the following:

select A.id as aid, B.id as bid from A, B, where ...

So it's not a problem.

>  2- xxxxid => short option, may result in hard readability.
>  3- xxxxId => we haven't decided if we want UC in field names. I won't
> discard this option, but does not favour it.
>  4- xxx_id => _ still undecided in field names, I'm not sure it's much
> better than the xxxid one

These three examples are only a matter of taste whithout any advantages
or disadvantages.

>  5- xxxxnumber => number is quite long, and may result in very long
> field names, which is not good. I does not like this option.
> 
> Let's start the discussion

Since the name of a columns is actually not of any importance, from a
technical/database view, why impose restrictions on how to use them?

Not the column name, but the table schema should indicate whether
something is a primary key or not.

That said, I can live with any of above naming schemes, but I would
prefer to not have a rule on this.

I'd prefer [1] or [4], though.