Entity Relationship Diagrams (Library Example)

DJMK4

DJMK4

DJMK4

Soldato
Joined
1 Dec 2004
Posts
22,716
Location
S.Wales
Im trying to get my head around this, iv been pondering on this for a while now and not 100% sure if im on the right track.

Given the library system example with books and the loaning of these books, a customer can lend a book from a library, a library may contain several copies of the same book, but does this look right?

erd.JPG


1)One customer can have many loans
2)Many Loans may feature 1 book
3)One Book may have many copies
4) One copy may have many reservations

Thing i have a problem with is the many loans may feature one book? also number 3, the book does not have many copies?
 
well its saying it asif they had 3-4 copys of one book (ie lord of the rings)
so while load of the rings may be on loan there are 3-4 copy of it so it could be on loan 3-4 times

the diagram shows only 1 box for the book but realy its not ONE item its meny
 
Seems odd that "Copies" is related to "Reservations", but "Books" to "Loans".

Personally I think "Copies" should be related to "Reservations" and "Loans", and "Books" related to "Copies" only.
 
DannyDan said:
Seems odd that "Copies" is related to "Reservations", but "Books" to "Loans".

Personally I think "Copies" should be related to "Reservations" and "Loans", and "Books" related to "Copies" only.
Click to expand...

I'd connect loans to copys (as each copy can only have 1 loan) and reservations to books (as you can reserve a book but get any of the copys)
 
Ahh yeah thats better, i see the principal of it all now, ill stick it all in access and link up the key fields and see what i get.

Cheers :)
 
[Sniper][Wolf] said:
Ahh yeah thats better, i see the principal of it all now, ill stick it all in access and link up the key fields and see what i get.

Cheers :)
Click to expand...

I hate databases
was REALY good at it back in 99 but I've forgotten ALL of it now.

damnit
 
Surely if it's a reservation as in allocated to you then it's a copy, not a book. i.e. if a copy of a book is reserved for you then no-one else can take it out on loan? Been a while since I went to a library though :o

I think DannyDan had it right?
 
MookJong said:
Not another Relationship thread :rolleyes:

VeNt is correct
Click to expand...

:D lol....nice one!

I love Db's. I program but they're my speciality.
Used to hate it at Uni but I work with them constantly now so I've got well into it all.
 
[Sniper][Wolf] said:
Thing i have a problem with is the many loans may feature one book? also number 3, the book does not have many copies?
Click to expand...

I'd read that as 'one book can have many loans', i.e., a book may have been taken out 100 times in its life.

As for 3, I'd take it as one book (not a physical book, but a name of book) may have several copies. I.e., (taken from the front page of the rainforest place) The library may have 10 copies of The Da Vinci Code. This then makes the previous link sound wrong :)
 
sist_si said:
:D lol....nice one!

I love Db's. I program but they're my speciality.
Used to hate it at Uni but I work with them constantly now so I've got well into it all.
Click to expand...

what kinda courses did you do at college/uni to get into DBA?
 
VeNT said:
what kinda courses did you do at college/uni to get into DBA?
Click to expand...

I did BSc Computing at Uni....it taught me the principles of DB's (amongst other things) but I'll admit I wasn't the most academic person at uni...the social life was more of a draw for me. Anyway, we covered MS Access, VB and VBA type stuff.

I got into web and windows app development when I started to work and it all spanned from there. Plus, I've only worked for small companies so I develop for the entire development life-cycle which also gives you loads of experience.
Jump in at the deep end I say, practice makes perfect and all that.
 
So to sum up

One customer can have many loans
Many Loans can contain one copy
One copy can contain many books
One Book can contain many reservations

?
 
[Sniper][Wolf] said:
So to sum up

One customer can have many loans
Many Loans can contain one copy
One copy can contain many books
One Book can contain many reservations

?
Click to expand...

Yea, but the more I think about it, the more I think you would want to track reservations against actual 'copies' of the book rather than the book itself.
From a management point of view that might be better. At the end of the day though, there are probably a few ways to do it so the choice is yours.

I've not really thought this all the way through and i don't have much library experience either BUT...you could scrap the reservations table altogether and have the loans table manage loans and reservations (with some kind of type flag so you know what's what)....possibly under a more appropriate name also....I think that's probably confusing the issue a bit tho :)
 
Customers - [CustomerID], Name
Books - [ISBN], Title
Copies - [CopyID], Books.ISBN

Loans - [Copies.CopyID], Customers.CustomerID
Reservations - [Customers.CustomerID], [Books.ISBN]

That's how I'd set it up. Bolded = primary keys (/foreign or whatever).

In the loans table only CopyID is unique- therefore a copy of a book may only be loaned to one person at one time. In the reservations table several customers may reserve one book, but one customer cannot reserve a book twice, etc.
 
Last edited:
playworker said:
Surely if it's a reservation as in allocated to you then it's a copy, not a book. i.e. if a copy of a book is reserved for you then no-one else can take it out on loan? Been a while since I went to a library though :o
Click to expand...
AFAIK a reservation is made when you want to loan a book that is already out on loan. There may be many copies of the book, but they are all out on loan.

When you make a reservation, you don't reserve a specific copy of a book, just the first copy to be returned. I think :o
 
You must log in or register to reply here.
Back
Top Bottom