[Koha-devel] Koha db schema pecularities

[LAURENT Henri-Damien]

Le 06/09/2011 19:23, Jared Camins-Esakov a écrit :
> Hello.
Hi,

> 
> I was looking at the schema that Koha uses for its database, and noticed
> a few peculiarities that I wonder if anyone could shed some light on:
> 
> The serial table uses VARCHAR(100) for serial.biblionumber and
> serial.subscriptionid. 

> Is there any context in which those are not
> numeric identifiers? 
I donot think so.
They should indeed be INT or BIGINT.
> Presumably as a result of that, there are no
> foreign key constraints on the serial table. I think there probably
> should be.
you are right and even, serial.biblionumber should be removed from taht
table since it is in subscription table.

> 
> There's a similar situation with the subscription table:
> subscription.biblionumber doesn't have a foreign key relation with
> biblio.biblionumber, nor, indeed, any index at all.
same here.

> Foreign keys are
> also lacking for subscription.aqbooksellerid and subscription.aqbudgetid.
I think we (BibLibre) have some interesting ongoing work wich copes with
serials acquisitions which could result in getting rid of that data.

> 
> On the subject of foreign keys, should items.itype have a foreign key
> linking it to itemtypes?
Could be discussed. But items.itype was introduced in 3.0 in order to
store itemtype in addition with ccode (circulation/collection code).
I think that items.itype should stick to itemtype. But since it CAN be
NULL sometimes when you donot use itemtypes, then creating a foreign key
would be too much compelling. You surely would not like to see the data
not stored silently because of a FK check fail on that data.
Koha was meant to manage mandatory tags/subfields from the interface and
not at a database level (from what I know of  it).

> 
> My understanding of foreign key relationships is that they're a Good
> Thing because they provide a means to ensure referential integrity. Am I
> missing something in these particular cases (and there are probably
> others, these are just the ones I happened to notice), or would people
> say this was probably just an oversight?
> 
> Regards,
Regards.
-- 
Henri-Damien LAURENT