can you insert a join?

normalization question on data base structure

[Baruch Menachem]

I want to build upon a library application and the normilization has me stalled because I don't know if you can update two tables at once.

I have one table for books, and another for clients. I think the book should have check out status for each volume, but should I add the due date and the client ID onto the book table or should I have a separate table for checked out material with a client id, book id and due date? The first way seems a bad idea, as there will be lots of null data on the book table, but I haven't seen how to do an update to one table and insert into another with the same command. Is it ok to do separate commands to two linked table like that? Is seems a major rule breach somehow.

Thanks

[mattur]

Generally, it's a good idea to design the normalised database structure first (ie with no repeated/redundant data), then look at denormalising it if required. See these posts on normalisation [webmasterworld.com] and de-normalisation [webmasterworld.com]

You don't mention what database you are using, but most databases support transactions that can be used to bundle together two or more SQL updates. Or you can enforce multiple updates at the application level.

In a fully normalised database design you would use a third Many:Many table, as you mention, for books borrowed (eg a "Loans" table). This would have ClientID, BookID, DateBorrowed, DateDue, DateReturned fields etc.

A fully normalised design would not put loan information in the books table. You can still query the database for books available etc with slightly more complex SQL eg:

SELECT * FROM Books WHERE BookID NOT IN (SELECT BookID FROM Loans WHERE DateReturned IS NULL)

You may choose to denormalise the database design to make common queries (eg available books) quicker/easier. You could just add one field to the books table for this eg a boolean "OnLoan" field, and still use the third table Loans for the loan data. You could then use SQL transactions to update the Books and Loans tables together to maintain consistency.

[ZydoSEO]

If you have 3 copies of Book X, do each of the books have a unique ID like a barcode or something? Or are all 3 books identified the same way? The answers to those types of questions will guide what you can/should do with your schema.

[Baruch Menachem]

Since this is just a sort of student project, I hadn't thought that far....
I think on reflection that each book should have its unique ID. Since this is a library sort of deal, where one of the fields is 'checked out,' that means each copy would be unique.

So that might mean I should drop the checked out column, and add a table for volumes, book id, volume id where we have a one to many relationship on that.

One big glaring black hole in the scheme so far is the way I designed the author part. Since each line is unique, and I have a column for author id, I now have the problem of what to do with a book with many authors. My author author id schema has a field for a first name, last name and one author id. The author id is primary key and auto increment.... should I instead have a seperate table to handle that instance? I have to re think that too.

I did find an interesting web page on transactions. They are actually kind of fun.

I think I will do a table alter later on using Mattur's advice. I notice in my select, I never even use the 'checked out' column for books that are out.
What I have now is

mysql> select concat(clients.fname,' ',clients.lname) as client,
 -> books.title,
 -> date_format(checkout.date_due,'%W %M %D %Y')
 -> as due_date
 -> from books inner join checkout using(bood_id)
 -> inner join clients using(client_id); 
+-----------------+--------------------+-----------------------------+
¦ client  ¦ title  ¦ due_date   ¦
+-----------------+--------------------+-----------------------------+
¦ Jim Egger ¦ Biblical Literacy ¦ Monday January 19th 2009 ¦ 
¦ Gloria Wallberg ¦ Origin of Speicies ¦ Monday January 19th 2009 ¦ 
¦ Gloria Wallberg ¦ African Genisis ¦ Monday January 19th 2009 ¦ 
+-----------------+--------------------+-----------------------------+

This has been fun, and I just want to say a word of thanks to everyone who has put up with my n00bishness with this.

[edited by: Baruch_Menachem at 4:38 am (utc) on Dec. 30, 2008]