Hello. 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? Presumably as a result of that, there are no foreign key constraints on the serial table. I think there probably should be. 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. Foreign keys are also lacking for subscription.aqbooksellerid and subscription.aqbudgetid. On the subject of foreign keys, should items.itype have a foreign key linking it to itemtypes? 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, Jared Camins-Esakov -- Jared Camins-Esakov Bibliographer, C & P Bibliography Services, LLC (phone) +1 (917) 727-3445 (e-mail) jcamins@cpbibliography.com (web) http://www.cpbibliography.com/
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
participants (2)
-
Jared Camins-Esakov -
LAURENT Henri-Damien