Database design - Entity relations

Discussion in 'Scripting & Programming' started by Mikel, Nov 28, 2007.

  1. Mikel

    Mikel New Member

    6
    0
    15
    I'm currently designing a simple database for a library(a university exercise) with 3 tables: Books, Loans and Users. All 3 tables have predictable attributes e.g. books have their ISBN, title etc.

    First of all i'm wondering whether the ISBN or Barcode would make a better primary key in the books table. Each ISBN can have multiple copies of the same ISBN, but each barcode on these copies are unique. I might have answered my own question here but clarification would be appreciated.

    Secondly, would the Loans table be suitable for the fields containing the maximum amount of books allowed per user and a reservation for a book be stored or within the user table seeing as it is user related info.

    Thanks anybody willing to help/has a clue what i'm on about.:biggrin
     
    WIP: CCNA, Computer Networking Bsc
  2. greenbrucelee
    Highly Decorated Member Award

    greenbrucelee Zettabyte Poster

    14,292
    265
    329
    I havent done any Database for about 9 years but I'll have ago

    1st Answer: Barcode should be your unique identifier as no other barcode is the same
    2nd Answer: The loans table should be used for the maximum amount of books allowed.
     
    Certifications: A+, N+, MCDST, Security+, 70-270
    WIP: 70-620 or 70-680?
  3. BosonMichael
    Honorary Member Highly Decorated Member Award 500 Likes Award

    BosonMichael Yottabyte Poster

    19,183
    500
    414
    I thought the ISBN was unique as well. If that's not the case, then go for the barcode (UPC?).

    I would have thought that the maximum books for each user would be stored in the Users table. For example, where would you store the value in the Loans table for how many books Michael could check out if Michael hasn't checked out any books?
     
    Certifications: CISSP, MCSE+I, MCSE: Security, MCSE: Messaging, MCDST, MCDBA, MCTS, OCP, CCNP, CCDP, CCNA Security, CCNA Voice, CNE, SCSA, Security+, Linux+, Server+, Network+, A+
    WIP: Just about everything!
  4. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,624
    117
    224
    If you can guarantee that the barcodes are unique, then use those. If it is possible that somebody can make a mistake and duplicate a code then either put a proceedure in place to replace the barcode, or use a separate 'serial' as a key.
    Depends if the max number is variable, or fixed for everybody. If variable then it belongs with the user. If it is the same for everybody then idealy it should live in its own table.
    For reservations I'd put them in a separate table, similar to the loans table.

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  5. MacAllan

    MacAllan Byte Poster

    249
    6
    30
    My thoughts:

    Barcode, - or autoincrement in case you have stock that isn't barcoded.

    If the maximum number of loans varies with different users, than in the User Table. If it just varies, say between children and adults, then no field, just a rule in whatever form is appropriate linked to the d.o.b. or user-category.

    I'd put reservations with Loans if you were only allowed those tables, as it would be easier to stop someone else borrowing a reserved book.
     
    Certifications: A+, N+, CCNA
    WIP: CCNP, Linux+
  6. greenbrucelee
    Highly Decorated Member Award

    greenbrucelee Zettabyte Poster

    14,292
    265
    329
    There is a term which I forget which an entity can be used as I unique identifier but can also have another value this why I say barcode.

    As for the max amount of book allowed then why not create a 4th column with variable perameters it'll save a lot of bother.
     
    Certifications: A+, N+, MCDST, Security+, 70-270
    WIP: 70-620 or 70-680?
  7. greenbrucelee
    Highly Decorated Member Award

    greenbrucelee Zettabyte Poster

    14,292
    265
    329
    What are you creating this on, is it Oracle?

    as thats the only dbase I remember doing.
     
    Certifications: A+, N+, MCDST, Security+, 70-270
    WIP: 70-620 or 70-680?
  8. Mikel

    Mikel New Member

    6
    0
    15
    Woah, fast replys. Thanks all. Some very valid points about the maximum amount of books allowed. Each user is permitted a maximum of 5 at any one time, so i'm assuming creating its own table would be suitable?

    I'm using Access. I've never used Oracle but can't imagine they would be that much different????
     
    WIP: CCNA, Computer Networking Bsc
  9. MacAllan

    MacAllan Byte Poster

    249
    6
    30
    You don't even need a field if it's a constant, and certainly not a table. Maybe we're suggesting things you haven't covered in your course yet.

    Heh: they are Massively different :)
     
    Certifications: A+, N+, CCNA
    WIP: CCNP, Linux+
  10. zebulebu

    zebulebu Terabyte Poster

    3,748
    330
    187
    LOL - Best statement of the year!

    Seriously, whilst they're both 'databases' (or, more strictly, RDBMSes), they bear about as much similarity to each other as a Nissan Micra does to a Bugatti Veyron - you know they're both technically 'cars' but thats about as far as it goes!

    For the Primary key field I would use the ISBN - this IS a unique number - there are international standards in place to ensure this is the case, therefore you shouldn't have any duplicate entries to worry about. You may have more than one copy of a book - but you only need to remember the principles of ACID and make sure you apply them (Atomicity, Consistency, Isolation, Durability). In this case, the 'book' table would be considered perfectly 'correct' theoretically if you defined each individual entity by its ISBN. There is a school of thought that say its always best to use a completely extraneous value for a PK - I'm not in that school! For my money, it makes far more sense to identify something by one of its 'true' attributes than some ephemeral made-up number that just adds another field to a table.
     
    Certifications: A few
    WIP: None - f*** 'em
  11. Sparky
    Highly Decorated Member Award 500 Likes Award

    Sparky Zettabyte Poster Moderator

    10,718
    543
    364
    :eek:

    that is all.... :biggrin
     
    Certifications: MSc MCSE MCSA:M MCSA:S MCITP:EA MCTS(x5) MS-900 AZ-900 Security+ Network+ A+
    WIP: Microsoft Certs
  12. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,624
    117
    224
    If you have two (or more) copies of a book where the ISBN is the same how do you distinguish them? This assumes that for stock purposes each copy is 'different'. If you don't care about this then the key isn't unique, which may or may not matter, but makes life difficult for the application part!

    This is why I suggested that the barcode was a better bet.

    Harry (not getting into the 'natural' key/'surrogate' key debate - I believe that both are useful)
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  13. ffreeloader

    ffreeloader Terabyte Poster

    3,661
    106
    167
    I would just use a couple of columns. One for the total number of copies, and one for the number of copies available. Yeah, it's a little more complex, but it makes the database much more usable in that you can look in the database to see if any copies of a specific book are on the shelf.

    It would also make it possible to find total number of books available, total number of discrete books, total number of discrete books available, etc....
     
    Certifications: MCSE, MCDBA, CCNA, A+
    WIP: LPIC 1
  14. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,624
    117
    224
    That indeed is a possible alternative, although I would derive the 'books available' from a join of books stocked and loans.

    As so often happens in such a project - it isn't fully defined! :biggrin

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  15. ffreeloader

    ffreeloader Terabyte Poster

    3,661
    106
    167
    Why use a join? More efficient?
     
    Certifications: MCSE, MCDBA, CCNA, A+
    WIP: LPIC 1
  16. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,624
    117
    224
    Because redundant info is always a problem in a database.

    If you have a count of a particular book, and a count of loans for that book, you can either calculate the shelf stock from a join 'on the fly' or keep the value in the books table. This latter means that on every loan and return you have to update that value with a transaction surrounding the loans and books table updates. Any other operation must have consideration of keeping this value 'on track'.

    If you do it on the fly you are only updating the loans table.

    I have always understood that keeping redundant info is 'bad. I fully recognize that there are exceptions to this - particularly where the join is expensive.

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  17. ffreeloader

    ffreeloader Terabyte Poster

    3,661
    106
    167
    But how is doing it with keeping track of the number of books for any one ISBN number creating redundancy of data? You're not creating a duplication of ISBN numbers. You're just putting a number in a column for how many total copies of that book title are in the library, and how many are still on the shelf in another column. It satisfies ACID, at least the way I see it. Given, it's been a long time since I studied database design, but I just don't see what you're saying.
     
    Certifications: MCSE, MCDBA, CCNA, A+
    WIP: LPIC 1
  18. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,624
    117
    224
    Because you also have a count of the books out (effectively) in the Loans table.

    There are 3 items of info here:
    Total books stocked
    Total books on loan
    Books on shelf

    Given any 2 you can work out the 3rd. This means that if you record all 3 items then you need to put in stuff to make sure they track each other, otherwise your data would become inconsistant. IMHO doing that is bad design.

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  19. ffreeloader

    ffreeloader Terabyte Poster

    3,661
    106
    167
    OK. That makes sense from that perspective. It's just been so long since I did a database design I didn't see it.
     
    Certifications: MCSE, MCDBA, CCNA, A+
    WIP: LPIC 1

Share This Page

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.