MySQL Library query design

[weebulgirl]

Hi all, hopefully the subject is not extremely vague.

I am trying to code a php/mysql page as a library sort of application.

Database design is:

table library

id | title | author | isbn | copies | owner

 

table libstatus

record | id | borrower | checkout | checkin

 

The id in table library is auto_increment and is used in the id column for libstatus.  Record in libstatus is also auto_increment, primary key.

 

So, I know I could do something like:

foreach (allbooks as book)

{

if (count(select record from libstatus where id = book) > (select copies from library where id=book))

echo "book is not available";

}

 

(sorry for the bad psuedocode)

But I would really like to do some sort of a query that returns all books that are currently checked out, without having to cycle through.  It's been a while since I've worked with SQL..  anybody know what to do?

 

[corbin]

I would probably store each copy of a book as a separate row.  I would probably do the schema something like this:

 

 

CREATE TABLE books_info (

    isbn int NOT NULL AUTO_INCREMENTING PRIMARY KEY,

    title varchar(255),

    author_id int,

    description text

);

 

(author_id could be mapped to another table called author_groups or something which would contain author groups [obviously] since some books have multiple authors)

 

 

Then I would possibly have a books table where each physical book would have an entry.

 

 

And then, I would use book_id from the books table in books_status.

 

 

 

 

Could be a better way though.