[Koha-devel] [Koha] null itemnumbers and borrowernumbers in issues table

[Chris Cormack]

2009/4/11 Joe Atzberger <ohiocore at gmail.com>:
>
>
> On Fri, Apr 10, 2009 at 11:19 AM, Jeffrey LePage <jeffrey_lepage at yahoo.com>
> wrote:
>>
>> Greetings,
>>
>> We've just discovered 4 records in the issues table that have nulls where
>> there should never be nulls.  Specifically, 2 records with null
>> borrowernumber and 2 records with null itemnumber.  As a result, there are
>> two overdue books checked out to a NULL patron and two patrons who've
>> checked out NULL books.
>>
>> I notice that the issues table is constructed thus:
>> mysql> desc issues;
>> +-----------------+-------------+------+-----+-------------------+-------+
>> | Field           | Type        | Null | Key | Default           | Extra |
>> +-----------------+-------------+------+-----+-------------------+-------+
>> | borrowernumber  | int(11)     | YES  | MUL | NULL              |       |
>> | itemnumber      | int(11)     | YES  | MUL | NULL              |       |
>> | date_due        | date        | YES  |     | NULL              |       |
>> | branchcode      | varchar(10) | YES  |     | NULL              |       |
>> | issuingbranch   | varchar(18) | YES  |     | NULL              |       |
>> | returndate      | date        | YES  |     | NULL              |       |
>> | lastreneweddate | date        | YES  |     | NULL              |       |
>> | return          | varchar(4)  | YES  |     | NULL              |       |
>> | renewals        | tinyint(4)  | YES  |     | NULL              |       |
>> | timestamp       | timestamp   | NO   |     | CURRENT_TIMESTAMP |       |
>> | issuedate       | date        | YES  |     | NULL              |       |
>> +-----------------+-------------+------+-----+-------------------+-------+
>>
>> Notice that borrowernumber and itemnumber are NULL'able.
>> Isn't this a _bad_ idea?  I don't know why/how the null itemnumbers and
>> borrowernumbers got into the database, but it seems that making them
>> non-nullable would be a good idea.
>>
>> Comments?
>
> This is a known issue, basically a compromise to allow offline circulation
> to work in 3.0 (where the borrowernumber or itemnumber might not actually
> exist when the insertions are done).  But in short, yes, I agree DB
> constraints need to be imposed for itemnumber and borrowernumber in issues.

Shifting to the devel list.

Shouldn't we store the offline circs in their own table, with nullable
fields if they need it. Then reconcile them into the issues table?
That way we can put the constraints back on the issues table.

Chris