1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Entity Relationship Diagrams (Library Example)

Discussion in 'General Discussion' started by DJMK4, 8 May 2006.

  1. DJMK4

    Capodecina

    Joined: 1 Dec 2004

    Posts: 21,941

    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?

    [​IMG]

    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?
     
  2. VeNT

    Capodecina

    Joined: 9 Jan 2003

    Posts: 20,695

    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
     
  3. DannyDan

    Mobster

    Joined: 6 Jun 2005

    Posts: 2,669

    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.
     
  4. VeNT

    Capodecina

    Joined: 9 Jan 2003

    Posts: 20,695

    Location: Cornwall

    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)
     
  5. DannyDan

    Mobster

    Joined: 6 Jun 2005

    Posts: 2,669

    Location: Wirral, UK

    Yup, good point, you're right :)
     
  6. sist_si

    Wise Guy

    Joined: 28 Nov 2004

    Posts: 1,224

    Location: Birmingham

    yep, loans to copies is the way forward.
     
  7. VeNT

    Capodecina

    Joined: 9 Jan 2003

    Posts: 20,695

    Location: Cornwall

    but with reservations to books
     
  8. sist_si

    Wise Guy

    Joined: 28 Nov 2004

    Posts: 1,224

    Location: Birmingham

    ;) yep!

    Customers to Loans
    Loans to Copies
    Copies to Books
    Books to Reservations
     
  9. DJMK4

    Capodecina

    Joined: 1 Dec 2004

    Posts: 21,941

    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 :)
     
  10. VeNT

    Capodecina

    Joined: 9 Jan 2003

    Posts: 20,695

    Location: Cornwall

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

    damnit
     
  11. MookJong

    Sgarrista

    Joined: 20 Mar 2006

    Posts: 8,035

    Not another Relationship thread :rolleyes:

    VeNt is correct
     
  12. playworker

    Gangster

    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?
     
  13. sist_si

    Wise Guy

    Joined: 28 Nov 2004

    Posts: 1,224

    Location: Birmingham

    :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.
     
  14. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,195

    Location: Derbyshire

    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 :)
     
  15. VeNT

    Capodecina

    Joined: 9 Jan 2003

    Posts: 20,695

    Location: Cornwall

    what kinda courses did you do at college/uni to get into DBA?
     
  16. sist_si

    Wise Guy

    Joined: 28 Nov 2004

    Posts: 1,224

    Location: Birmingham

    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.
     
  17. DJMK4

    Capodecina

    Joined: 1 Dec 2004

    Posts: 21,941

    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

    ?
     
  18. sist_si

    Wise Guy

    Joined: 28 Nov 2004

    Posts: 1,224

    Location: Birmingham

    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 :)
     
  19. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,195

    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: 8 May 2006
  20. DannyDan

    Mobster

    Joined: 6 Jun 2005

    Posts: 2,669

    Location: Wirral, UK

    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