Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Library database design (2000)

    Someone is designing a database for a company library. They have tables for Books, Authors, Borrowing, and Staff (who borrow the books), with a one-to-many relationship between Books and Authors. I think this is wrong. This should be a many-to-many relationship with a join table between which holds a BookID (ISBN) and AuthorID. Would you agree?

    How would you deal with the issue of having several copies of a particular book? I was thinking of adding a field NoHeld and using calculations on forms (or DLookups?) to ensure that staff cannot borrow more books than are available? Any other suggestions? Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Library database design (2000)

    If one book can have multiple authors, you need a many-to-many relationship and hence an intermediary table. If you only register the first author of each book, you don't need the intermediary table.

    I would create an individual record for each book, even if there are multiple copies of the same title. That makes it possible to keep track of where each copy is; you wouldn't be able to do that if you only store unique titles.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Library database design (2000)

    I think that when we use the word 'book' we jump between two different meanings. Sometimes we use it to refer to the title, and sometimes to refer to the copy.

    Different copies of a book may be different editions, with different dates, different publishers, different format and also (in the library) different due date etc so I would definitely keep a separate record for each copy, . This table could use Accession Number as its key.

    But you could also have a titles table, which would make it clear that each copy is a copy of the same title.
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Library database design (2000)

    The database I'm discussing is to be used for training purposes, and I don't think it a good idea to teach Access by referring to an 'unsound' database design. What problems does having a one-to-many relationship between books and authors create?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Library database design (2000)

    The problem here is not that a one-to-many relationship is poor database design - it is just not a accurate model of reality. With a one-to-many relationship, each book can have only one author, whereas in reality they can have multiple authors.

    There is nothing wrong with choosing to simplify your model of reality, particularly for a training database, as long as you are clear about the choices you make, and what options you loose by doing it.

    In a library, this one-to-many relationship means that you would need to choose and accept that second and subsequent authors weren't recorded.

    A problem with making this choice, is that later, people might decide they don't like it and start to find ways around the limitation, that cause other problems. They might enter a book more than once so they can enter each of its authors, or else they might make up a new author with a concatenated name as a way of including both authors in the one field.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •