Mobile Book Library

[Brokit]

Hi All.

First of all, in case your are also following the MySQL Forum over at mysql.com, I also posted this question there.

I am not a SQL Professional and therefore I have a little question regarding advanced Database Design. It is trivial matter but nonetheless interesting.

I want to send out 4 or 5 books to a defined nummer of people and keep them floating around the community untill everyone had the chance to read them. The procedere would be to receive a book, read it, look into the Database who is next in line and send it off.

What implies this? I would like to get an more or less even avarage book load per person. People who already read a book shouldn´t, of course, receive the same book again. Neither should they be at the same hierachical position for another book and receive 2 or more books at the same time.

I guess, I am lacking the experience to design this effectively. What I would do would be to create a table like this.

| book_id | person_id | sort_order | read_flag | comment | send_off_flag |
*************************************************************
| 1 | 3 | 1 | yes | great | yes |
| 1 | 4 | 2 | yes | great | no |
| 1 | 2 | 3 | no | | no |
|||||||||||||||| at the same time further down the line ;-) |||||||||||||||||||
| 2 | 1 | 1 | yes | ok | yes |
| 2 | 4 | 2 | no | | no |


Try to make sure that a person only exists once per book_id. Having a different sort_order for each book_id grouped entries (the persons for one book) while avoiding to have persons at the same sort_order position in different book_id´s (this implies that everyone has a similar reading speed which is not very realistic. Any other suggestions?).

Of course I don´t expect you to do the work for me but maybe some of you have some good ideas for this or now of a good tutorial that deals with this kind of problems.

Thanks for reading!

Elmar

 

[LittleSmudge]

How about

Table for People

tblPerson
PersonId
FName
SName
etc ..


Table for Books
tblBook
BookId
Title
ISBN
etc ..

Table for Whos got which Book

tblLoan
PersonRef Joint
BookRef Prime Key
OutDate
ReturnDate



So when a person gets a book on loan you populate tblLoan with their PersonId and the BookId and the date that they were sent the book

When they want to hand it on you then create a query that combines tblPerson with tblLoan

Exclude all those who have already had this particular book
and sort the rest based on the time since they last returned a book.


qryHaveHadBook
SELECT PersonId
FROM tblPerson
INNER JOIN tblLoan
ON tblPerson.PersonId = tblLoan.PersonRef
WHERE BookRef = BookIdToBeRedirected

qryNeedsBook
SELECT PersonId, FName, etc ..
, Max(Nz(ReturnDate,01/01/2005)) As LastReturn
FROM tblPerson
INNER JOIN tblLoan
ON tblPerson.PersonId = tblLoan.PersonRef
WHERE PersonId NOT IN (qryHaveHadBook)
GROUP BY PersonId, FName, etc ..
ORDER BY LastReturn


The NZ(,) bit will put people who have never had a book at all at the top of the list.
Add whatever other fields you need from tblPerson where the 'etc ..' is.

You could fiddle around and get it all into one query if you want to - but this is just my 'off the cuff' approach for a starter.


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.