Ooookay. Thanks all! Many ideas, but 1st port of call has to be a server. Doesn't ALUG have a server connected to UEA's JANET? Isn't that what we'd want?
Had a look at http://www.uklinux.net/membership/
Free MySQL / PostgreSQL / PHP /Perl is for account holders calling from a LoCall number (0845 ono). If you dial up via other ISP's you don't get the databases. Well you can for £25 a year. That's for a single account; don't know what uklinux would think of a group of people using 1 account. Looks useful though.
Postgres seems to have more approval over MySQL. What's the reason behind this?
Also, I hold no Postgres documentation. This will cramp my style as I've no net connection evenings. Anyone got some PostgreSQL books?
Have been following up suggestions & stuff on web. Could use koha - could use a nice PHP+MySQL app MyOrgBook (http://www.it-firm.com). These can show solutions / be a source of ideas; personally would like to be creative rather then copy. NFIT! How is Zeus??
I see admin type roles as something to avoid; suspect we can't eliminate completely but do have some thoughts. ASSUMING we trust ALUG people who've been through some security protocol (is this a can of worms I see before me?) then we might assign permissions by context of what they want to do. E.g. current holder of a book has authority to return it or assign it to a requestor. If we gave this some thought might be able to do without the librarian role. See my ravings below.
Want to minimise demands at client end (Java?). Who is expert to adjudicate on this?
This all seems to nicely fall into the classic client / server duties ...Brett, are you volunteering to do the client / GUI side of things?
Dave, need to talk more about this - what's the form for big posts on such stuff? I am peripatetic so don't have web/IRC to hand - days can pass before I get posts. Hm.
Anyhow glue technology consensus seems:
Postgres - Java / JDBC - Perl
ALUGL Schema 0.1 is below - rip it to bits people! Why won't it work? Where is it cumbersome? It's fairly normalised - must it be more? Tricks to speed it up? Security considerations? Too complex? Something missing? [key things to look for - errors and omissions] Dave, I've not attempted to integrate your schema into this version.
ALUG Library Schema 0.1
Item Table ================================================== Item_ID Primary key: Auto incrementing numeric Item_Type 1=book, 2=mass media, 3=hardware, 4=hat etc Title char 128 (name of item) ISBN 16 char (??) Publisher 128 char (manufacturer) Author 128 char (model) Edition 6 char Published date YYYY-MM (bought date) Return Mode 1=back to ALUG, 2=wanders, 3=back to owner Last Transaction => pointer to Tx Table.Tx_ID <Danger Will Robinson> Owned By => pointer to Person Table.Person.ID Description 8096 char or note field Condition Last Seen 1=new, 2=good, 3=poor, 4=scrap/discard Last Seen datestamp Entry Created datestamp Entry Updated datestamp
Thus we can loan hats - or anything. They can return to ALUG always, the owner - or just wander about, person to person- more convenient that way.
<Danger> Last Tx - not normal form & redundant but handy. Alas it turns a mostly read-only table into a read/write - I think it has to go, to minimise locks and write problems.
Person Table ================================================== Person_ID Primary key: Auto incrementing numeric First Name 32 char Last Name 128 char Alias 32 char (alternative name) Password hmm. How to get this secure? How much security needed? Aka => pointer to Person Table.Person_ID (1st (last) entry is ZERO) Phone 16 char Email 32 char Entry Created datestamp (DO we need address info??)
Aka is about name changes etc. using a linked list; old name is still on system. Deleting old names dangles inbound pointers.
Tx Table i.e. Transaction Table =================================================== Tx_ID Primary key: Auto incrementing numeric Item_ID => pointer to Item Table.Item_ID Now With => pointer to Person Table.Person_ID Due Back YYYY-MM-DD Tx_Type 1=added to system, 2=borrowing, 3=discarded, 4=stocktake, 5=lost, 6=returned, 7=request Tx_Date datestamp
Every transaction gets an entry. Due Back might be auto-set or agreed on the spot - even blank for long-term loan.
Forms Needed ================================================== 1.1 Add Item 1.2 Edit Item
2.1 Add Person 2.2 Edit Person
3. New Transaction (loan, return, request, stocktake)
With a little thought, we may be able to do without much admin. eg: (current borrower | owner ) has permission to create a Return or Loan. Anyone can Add Item. Owner only can Edit Item. Anyone can Add Person. Person only can Edit Person or Add Person as an Aka.
Anyone can create a request.
Need a proper state diagram showing permissions at each point; need to inspect this to see if any state gets painted into a corner.
Queries ========================================== Where is Item? (= search for item) => links to Request & Loan Item transaction
When does Item return? => links to Request Item transaction
What has Person borrowed What has Person loaned out List all out List all in
List all Items due back in next X days List overdues
List Tx history Item, latest at top (could double as the search form) List Tx history Person (could double as the borrowings form)
Auto-email for overdues & Person-has-requested-item-you-hold would be nice.
ALUG would be created as a Person.
Quick! Save it before it gets away!
-Steve