[coder_pl]

ok so i need to create simple site using html/php/msyql where people are able to loan their books, its fairly simple idea, there are 5 people with few books each, which they want to loan, this site will look as following: dropdown menu to select 1 of 5 names then another dropdown with books avaliable (this menu depends on what name have been selected in the first one) then 2 text boxes to record name and email of who "borrowed" the book which then should not show in the dropdown menus.

Im bad at designing databases...and i am not sure it this would work:

Loaner {Name, Book_Owned} FOREIGN KEY (Book_Owned) REFERENCES Book (Book_ID));
Book {Book_ID, Author, Title, Publisher, Year, Avaliable(true/false}
User {Name, Email, Book_Borrowed} FOREIGN KEY (Book_Borrowed) REFERENCES Book (Book_ID));

Any help will be much appreciated!

[Dormilich]

I would try to remove the loan information from the Book and User table into its own.

Book (
  book_id, -- (PK), could be the ISBN
  owner,   -- (FK user_id)
  amount,  -- only if there is more than one book available
  author,
  title,
  publisher,
  year -- etc.
)

User (
  user_id, -- (unique)
  name, 
  email    -- (unique)
) primary key (name, email)

Borrowed (
  borrowed_by, -- (FK user_id) /* to make it more clear */
  book,        -- (FK book_id)
  date_out,
  date_in
)

via JOINs you can easily get all iformation you need:
- available books (from user): Book x Borrowed x [date] (x User)
- books from user: User x Book
- books loaned by user: User x Borrowed x Book x [date]

This post has been edited by Dormilich: 11 April 2013 - 12:01 PM

[coder_pl]

i think i didnt clear me self enough. Loaner and user are different people. I already know all 5 loaners and their books, user is just borrowing the book which is owned by loaner if you know what i mean...give that Dormilich your solution would work right?

[Dormilich]

coder_pl, on 11 April 2013 - 02:59 PM, said:

i think i didnt clear me self enough. Loaner and user are different people.

I don’t see the problem there. people that do not have books to loan (i.e. most borrowers, as even a loaner can borrow books from someone else) have no entry via FK in the Book table (i.e. Book x User = empty). and having an email address of the borrower doesn’t seem wrong.

[coder_pl]

I can not edit my posts so I have to post it again with corrections:

I think I did not clear me self enough. Loaner and user are different people. I already know all 5 loaners and their books, user is just borrowing the book which is owned by loaner, also the only data which is going to be recoreded into the database is the userName, userEmail and ofcourse which book did they "borrow" if you know what i mean...give that Dormilich your solution would work right?

[Dormilich]

coder_pl, on 11 April 2013 - 03:10 PM, said:

...give that Dormilich your solution would work right?

I’m certain it would work. the only thing to take care of are the insert queries (only loan books if they are available, which is just a date check in the Borrowed table). the rest is just a matter of cleverly constructed JOINs. I’d probably use VIEWs and TRIGGERs on them.

[Dormilich]

as an example: get all available books

-- something along that
SELECT
  Book.title AS title,
  User.name  AS owner
FROM
  Book
LEFT JOIN Borrowed
  ON Book.book_id = Borrowed.book
JOIN User
  ON Book.owner = User.user_id
WHERE
  -- not sure if I need the second condition
  NOW() > Borrowed.date_in OR Borrowed.date_out IS NULL

This post has been edited by Dormilich: 11 April 2013 - 06:38 AM

[coder_pl]

omg i read it so many times and i still dont get it i dont understand why in table "borrowed" there is "loaner", there should be a name of the person who is borrowing the book not who is lending the book, owner = loaner. Maybe i should have more tables to make it more clear to understand it i dont know why but i keep thinking that i need something like:

one for people with books (there is only 5 people with multiple books each)
one for books (there is 2 copies of one book owned by different people)
one for users which have borrowed some book

sorry for being such a pain but im just trying to understand it and i cant...

[coder_pl]

also user should be able to borrow more than one book, how would i store that in the DB? i can not insert new row with already existing user_id, name and email...

[Dormilich]

coder_pl, on 11 April 2013 - 07:39 PM, said:

also user should be able to borrow more than one book, how would i store that in the DB?

do another entry in Borrowed.

coder_pl, on 11 April 2013 - 07:39 PM, said:

i can not insert new row with already existing user_id, name and email...

that’s why I took the borrowing in its own table.

[Dormilich]

coder_pl, on 11 April 2013 - 07:30 PM, said:

omg i read it so many times and i still dont get it /> i dont understand why in table "borrowed" there is "loaner", there should be a name of the person who is borrowing the book not who is lending the book, owner = loaner.

blame it on my English¹. of course I meant the person who borrowed the book.

coder_pl, on 11 April 2013 - 07:30 PM, said:

i dont know why but i keep thinking that i need something like:

one for people with books (there is only 5 people with multiple books each)
one for books (there is 2 copies of one book owned by different people)
one for users which have borrowed some book

I would advise against that schema. I see no need to differentiate between people who own a book and people who borrow a book (because people who own a book can also borrow a book).

what you definitely need is a table for books and a table for users.

users who own a book just have their ID listed as owner of a book. plus you don’t have any trouble if there comes a 6th person with a couple of books.

what remains is a table that tells which user borrowed a book, but that information neither belongs to the books, nor the users, as it is constantly changing.

Quote

one for books (there is 2 copies of one book owned by different people)

easy to solve:

Book (
  isbn,
  owner, -- FK User.user_id
  title
  ...
) primary key (isbn, owner)

PS. you should read about Database Normalisation, there should be a tutorial here.




1 - in German, loan and borrow can be expressed with the same word (leihen)

[coder_pl]

ok i think im getting it a little bit now...how about the dates in borrowed table? how would i fill them out? i dont want to ask user to do it. Can I automaticly insert todays date(date when user borrow the book) for Date_Out and give like 10 days of loan on each book and automaticly insert that date and also make the book avaliable again?

CREATE TABLE User(
User_ID int UNIQUE AUTO_INCREMENT,
Name varchar(30) NOT NULL,
Email varchar(50) NOT NULL unique,
PRIMARY KEY (Name, Email));

CREATE TABLE Book(
Book_ID int primary key,
Owner int,
Author varchar(50),
Title varchar(200),
Publisher varchar(50),
Year year,
Quantity int,
FOREIGN KEY (Owner) REFERENCES User (User_ID));

CREATE TABLE Borrowed(
Loaner int,
Book int,
Date_Out date,
Date_In date,
FOREIGN KEY (Loaner) REFERENCES User (User_ID),
FOREIGN KEY (Book) REFERENCES Book (Book_ID));

[Dormilich]

coder_pl, on 11 April 2013 - 08:19 PM, said:

how about the dates in borrowed table? how would i fill them out? i dont want to ask user to do it. Can I automaticly insert todays date(date when user borrow the book) for Date_Out and give like 10 days of loan on each book and automaticly insert that date and also make the book avaliable again?

sure, no problem:

INSERT INTO Borrowed
(
  loaner, 
  book,
  date_out,
  date_in
)
VALUES 
(
  :user, -- user ID to input
  :book, -- book ID to input
  CURDATE(),
  CURDATE() + INTERVAL 10 DAY
)

you only have the problem that after 10 days (unless you update the value) the book automatically becomes available—even if it is still loaned.

and of course if the book is returned sooner, you also have to update the date_in value. that’s why I (personally) would prefer to leave the date_in as NULL until the book really was returned.

This post has been edited by Dormilich: 11 April 2013 - 11:38 AM