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
On Fri, 04 May, 2001 at 8:58 +0100, brodders@cwcom.net wrote:
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?
Nope, no janet machine -- it's hosted on Martyn Drake's boxen, I think. Martyn -- any chance of postgres? :)
Postgres seems to have more approval over MySQL. What's the reason behind this?
Postgres is simply a better database. (Yes, MySQL has its uses -- it's not bad for sites such as slashdot. But I wouldn't want to encourage use of it until they fix the glaring errors and ommissions in it.)
Also, I hold no Postgres documentation. This will cramp my style as I've no net connection evenings. Anyone got some PostgreSQL books?
http://looking-glass.usask.ca:82/users-lounge/docs/ and other mirrors hold the documentation as a downloadable bundle in a range of formats. Of course, if you're using Debian you can just type "apt-get install postgresql-doc"....
How is Zeus??
Zeus rocks, but is non-free and therefore probably not best suited to our budget.
If we gave this some thought might be able to do without the librarian role.
We should still have a master librarian. Can I respectfully suggest something monkey-shaped? Perhaps an orangutan?
Want to minimise demands at client end (Java?). Who is expert to adjudicate on this?
Multiple clients! Multiple clients!
Dave, need to talk more about this - what's the form for big posts on such stuff?
If you have a big post to make, or a large collection of random thoughts, why not lob them into a web page (perhaps on the alug site?) and cite a URL here?
ALUGL Schema 0.1 is below - rip it to bits people! Why won't it work?
Why won't it work? Because it's impossible to read in my mail client...
Item Table =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Item_ID=09=09=09Primary key: Auto incrementing numeric Item_Type=09=091=3Dbook, 2=3Dmass media, 3=3Dhardware, 4=3Dhat etc
.... looks suspiciously like an "enum"-style field. Item type should of course be a link to a seperate table of items, to allow us to add "holographic storage" or "atomic disk" or "symbiot" in the future...
Title=09=09=09char 128 (name of item)
Char bad. Text good. See Postgres documentation on datatypes.
ISBN=09=09=0916 char (??)
Is there a defined format for ISBN numbers and a way to validate them? It would be nice to add a trigger to that field to automatically validate new values and reject incorrect numbers...
Publisher=09=09128 char
Would be inclined to link this to a sep. table too, so we can list all books by O'Reilly for example (and avoid problems of people entering OReilly, ora, o'reilly, o'reily etc).
Published date=09=09YYYY-MM (bought date)
Published date and bought date != the same....
Return Mode=09=091=3Dback to ALUG, 2=3Dwanders, 3=3Dback to owner
Again, link to a sep. table? Anyone else use Dia? Might be worth knocking up the DB in that... or any other recommendations for db/modelling software? Andrew. -- All views are my own, who else would want them?
Is there a defined format for ISBN numbers and a way to validate them? It would be nice to add a trigger to that field to automatically validate new values and reject incorrect numbers...
Yes, I have something somewhere that describes how to calculate the check digit. And a Tcl script that does it... ..Adrian -- Dr Adrian F Clark http://esewww.essex.ac.uk/~alien/ alien@essex.ac.uk -- VOICE: +44 1206 872432 -- FAX: +44 1206 872900 Dept ESE, University of Essex, Wivenhoe Park, Colchester, C04 3SQ, UK
--- Andrew Savory <a.savory@btinternet.com> wrote:
On Fri, 04 May, 2001 at 8:58 +0100, brodders@cwcom.net wrote:
If we gave this some thought might be able to do without the librarian role.
We should still have a master librarian. Can I respectfully suggest something monkey-shaped? Perhaps an orangutan?
Are you trying to say something here? I keep the books ATM!!!!!!
Want to minimise demands at client end (Java?). Who is expert to adjudicate on this?
Multiple clients! Multiple clients!
ARRRGGGHHH!
Dave, need to talk more about this - what's the form for big posts on such stuff?
If you have a big post to make, or a large collection of random thoughts, why not lob them into a web page (perhaps on the alug site?) and cite a URL here?
Good idea, max post size should be 15K.
ALUGL Schema 0.1 is below - rip it to bits people! Why won't it work?
Why won't it work? Because it's impossible to read in my mail client...
Which Client are you using? <sniped sensible stuff>
Anyone else use Dia? Might be worth knocking up the DB in that... or any other recommendations for db/modelling software?
White board and a board marker? Thought of another table for my original schema item_type(Item_type no., Description, details etc...) Thanks D
Andrew.
-- All views are my own, who else would want them?
_______________________________________________ alug, the Anglian Linux User Group list Send list replies to alug@stu.uea.ac.uk http://rabbit.stu.uea.ac.uk/cgi-bin/listinfo/alug See the website for instructions on digest or unsub!
__________________________________________________ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/
We should still have a master librarian. Can I respectfully suggest something monkey-shaped? Perhaps an orangutan?
Are you trying to say something here? I keep the books ATM!!!!!!
This may be a Pratchett reference. Or it may just be something personal. ;-) ..Adrian -- Dr Adrian F Clark http://esewww.essex.ac.uk/~alien/ alien@essex.ac.uk -- VOICE: +44 1206 872432 -- FAX: +44 1206 872900 Dept ESE, University of Essex, Wivenhoe Park, Colchester, C04 3SQ, UK
--- brodders@cwcom.net wrote:
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?
JANET = ATM link = good.
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.
I have an account with them, used by me, my dad and my mum, not had any probs yet.
Postgres seems to have more approval over MySQL. What's the reason behind this?
/me got no RDBMS exp.
Also, I hold no Postgres documentation. This will cramp my style as I've no net connection evenings. Anyone got some PostgreSQL books?
Well get the uklinux account then and one fo these �195 PC's :o)
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 like creative, hense I wrote my own POP3 client last night.
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.
ADMIN is a MUST. We need a coordinator who has the ability to add entries.
Want to minimise demands at client end (Java?). Who is expert to adjudicate on this?
<fx action="RUNS"/>
This all seems to nicely fall into the classic client / server duties� ...Brett, are you volunteering to do the client / GUI side of things?
Thats what I like delegation. Once college finishes (june 8) I can have a go with the GUI/Client.
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.
Get a tripod account and put the pages on that.
Anyhow glue technology consensus seems:
Postgres - Java / JDBC - Perl
Sounds good, defeats the initial idea of learning mysql and the reason you borrowed the Mysql book!
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.
I like requests to rip things apart. So here goes...
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)
May need to be longer we could have long titles, 128 !=much
ISBN 16 char (??)
I think that 20 is the number for some reaso but can't think why.
Publisher 128 char (manufacturer) Author 128 char (model) Edition 6 char
Why not a simple number? i.e. 3 for third edition, 2000 for millenium eddition.
Published date YYYY-MM (bought date)
Like someone else said, do we need this? its all on amazon, Purchase date would be better.
Return Mode 1=back to ALUG, 2=wanders, 3=back to owner
No. A better way is to keep each item on loan, if the librarian has it the librarian has it put down as on loan to them.
Last Transaction => pointer to Tx Table.Tx_ID <Danger Will Robinson>
no need to store just do SELECT from LOANS WHERE item_id=<current ID> Don't store more than required
Owned By => pointer to Person Table.Person.ID
Does this mean you are planning for stuff not owned by ALUG to be loaned out? is this a good thing?
Description 8096 char or note field Condition Last Seen 1=new, 2=good, 3=poor, 4=scrap/discard
good point, didn't think of this, could have been put in the notes section.
Last Seen datestamp
just do the above SELECT.
Entry Created datestamp Entry Updated datestamp
Not sure about these being needed.
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.
But why not have the librarian as a member and who has all items not on ;loan on loan, if that makes sense.
<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.
I'm inclined to agree.
Person Table ================================================== Person_ID Primary key: Auto incrementing numeric First Name 32 char
I always allow 80 for each name.
Last Name 128 char Alias 32 char (alternative name) Password hmm. How to get this secure? How much security needed?
Ack.
Aka => pointer to Person Table.Person_ID (1st (last) entry is ZERO) Phone 16 char
17 is the max length for a phone number.
Email 32 char
which means my email only just work then david_freeman@rocketmail.com consider the max length for a domain name is 255 characters + .com etc...
Entry Created datestamp
do we need this?
(DO we need address info??)
Very much so, how else do I know who you are when your mail account is removed
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
These are Foriegn keys, obviously you are a C programmer.
Due Back YYYY-MM-DD
I wasn't going to have one of these, just put it in the client to check for all loans older than say a month.
Tx_Type 1=added to system, 2=borrowing, 3=discarded, 4=stocktake, 5=lost, 6=returned, 7=request
<confused/>
Tx_Date datestamp
is this needed?
Every transaction gets an entry. Due Back might be auto-set or agreed on the spot - even blank for long-term loan.
What about reservations?
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)
new reservations?
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.
I would be happier with only a few people being able to make changes, minimizes the risk of loozing stuff.
Anyone can create a request.
Reservations from anyone, all other methods must be private.
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!
if ALUG = a person in the table why not map all objects not on loan to members as on loan to the ALUG? Just some random Babblings. Thanks D
-Steve
_______________________________________________ alug, the Anglian Linux User Group list Send list replies to alug@stu.uea.ac.uk http://rabbit.stu.uea.ac.uk/cgi-bin/listinfo/alug See the website for instructions on digest or unsub!
__________________________________________________ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/
participants (4)
-
Adrian F. Clark -
Andrew Savory -
brodders＠cwcom.net -
David Freeman