Hi Gavin Gavin McCullagh wrote:
- The borrowernumber and itemnumber obviously relate to the primary keys in items and borrowers.
Yep
- Do all loans stay in this table indefinitely even after they are returned? ie is this where the loan history is kept?
Partially :-) See my answer to the questions below. The loans stay in this table after returned but its not where the totality of the history is kept.
- In terms of the dates, it looks like "date_due" is the current due date (duh!), "returndate" must then be the date the book is returned -- NULL initially, lastreneweddate is the date of last renewal. If this is so, do you store the date the book was first lent or not? Also, do you store the "renew history" of a given loan and where?
Yes, but not in the issues table. In the statistics table instead. The statistics table looks like this +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ | datetime | branch | proccode | value | type | other | usercode | itemnumber | itemtype | borrowernumber | +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ | 2006-08-05 13:01:23 | L | NULL | 0.0000 | issue | | NULL | 139167 | BJN | 8846 | | 2006-08-05 13:01:19 | L | NULL | 0.0000 | issue | | NULL | 136284 | BJN | 8846 | | 2006-08-05 13:01:16 | L | NULL | 0.0000 | issue | | NULL | 76508 | BJN | 8846 | +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+
- Do I guess correctly that there a single entry in this table per loan, not one for the initial loan and one for every renewal? Thats right a single entry per loan.
- What exactly goes in the "return" field?
Nothing currently.
- Is "renewals" an integer count of the number of times this particular loan has been renewed?
Yup
- Should the timestamp be updated every time any record is changed or at record creation?
MySQL does this for you.
- If I wanted statistics of how many loans/renewals were given out on a given day some time ago, could I do this? It appears not.
Yes indeed, using the statistics table EG select * from statistics where type='renew' and datetime > '2006-07-20' and datetime <='2006-07-21' limit 3; +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ | datetime | branch | proccode | value | type | other | usercode | itemnumber | itemtype | borrowernumber | +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ | 2006-07-20 09:34:55 | S | NULL | 0.0000 | renew | | NULL | 121757 | NULL | NULL | | 2006-07-20 09:41:06 | NULL | NULL | 0.0000 | renew | | NULL | 84985 | NULL | NULL | | 2006-07-20 09:41:25 | NULL | NULL | 0.0000 | renew | | NULL | 35666 | NULL | NULL | +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ 3 rows in set (0.01 sec) You can of course add and itemnumber=something as well. So on your import you might like to populate the statistics table also. Hope this helps Chris