Entity Relationship Diagrams (Library Example)

Soldato
Joined
1 Dec 2004
Posts
22,367
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?
 
Soldato
Joined
9 Jan 2003
Posts
21,000
Location
Cornwall
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
 
Soldato
Joined
6 Jun 2005
Posts
2,668
Location
Wirral, UK
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.
 
Soldato
Joined
9 Jan 2003
Posts
21,000
Location
Cornwall
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.

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)
 
Soldato
OP
Joined
1 Dec 2004
Posts
22,367
Location
S.Wales
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 :)
 
Soldato
Joined
9 Jan 2003
Posts
21,000
Location
Cornwall
[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 :)

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

damnit
 
Associate
Joined
16 Mar 2006
Posts
410
Location
Telford
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?
 

Pho

Pho

Soldato
Joined
18 Oct 2002
Posts
9,324
Location
Derbyshire
[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?

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 :)
 
Soldato
Joined
9 Jan 2003
Posts
21,000
Location
Cornwall
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.

what kinda courses did you do at college/uni to get into DBA?
 
Associate
Joined
28 Nov 2004
Posts
1,232
Location
Birmingham
VeNT said:
what kinda courses did you do at college/uni to get into DBA?

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.
 
Soldato
OP
Joined
1 Dec 2004
Posts
22,367
Location
S.Wales
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

?
 
Associate
Joined
28 Nov 2004
Posts
1,232
Location
Birmingham
[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

?

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 :)
 

Pho

Pho

Soldato
Joined
18 Oct 2002
Posts
9,324
Location
Derbyshire
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:
Soldato
Joined
6 Jun 2005
Posts
2,668
Location
Wirral, UK
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
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
 
Back
Top Bottom