library schema design

Let’s get back to the library system. First let’s look at the design. I have scanned in most of the books that we have on our bookshelves at home. I was surprised to find out that we have over 300 books. I didn’t scan the kids books in their room. My guess is that we have 600 books in the house. How did that happen? I imported these books into www.librarything.com so that I can auto populate most of the data by entering the ISBN number. I purchased a bar code scanner and found that the majority of the books include the ISBN number on the bar code. Many books have two bar code numbers, one for inventory control and one for ISBN. I selected librarything because it allows me to search the Library of Congress as well as Amazon to populate the library information. It allows you to enter the ISBN number, the book title, or the author name and it presents a list of matches. If there is no match in the Library of Congress, it allows you to search Amazon. I like this because between the two I found data for each book in our house. The biggest problem is that at times I found multiple copies because many books have multiple editions. A significant amount of the matches included a picture of the book cover which helped significantly.

The biggest benefit of this service is that it allows me to export the book information to a comma separated or tab separated file and import it into a database or Excel program.  The fields in this export are:

  • book id
  • title
  • author first and last name
  • author last and first name
  • other authors
  • publisher
  • date of publication
  • ISBN number
  • series
  • source (Amazon or Library of Congress)
  • language
  • language 2
  • original language
  • LC Call number
  • Dewey Decimal number
  • BCID
  • date entered
  • date entered
  • date entered
  • date entered
  • tags
  • rating
  • review
  • summary
  • comments
  • encoding

Looking at the data in this collection brings up a few questions. Why can’t I use Excel as the basis for this information? First, how do I record what is checked out and when it is checked out? Using one of the date entered and comments fields? I could but it would be difficult. The user who checks in and checks out books would need to be familiar with Excel and have a uniform way of checking in and checking out books. If multiple people do this using different fields or different formats, the data will become corrupted. We need to create a browser front end to make the data entry uniform and the way that data changes the same.

Second, if I wanted to sort the data using some index and perform a some simple analytics, an Excel spreadsheet will not work. If someone checks out a book and checks it back in, the record will be cleansed and lost if it is done in one spreadsheet. It would be nice at some time to find out what the most popular book is. It would also be nice to present a list of books that students have checked out and make it visible to parents and teachers. It gives both a tool to help educate the child and the librarian a starting point when they go to purchase more books. Analytics like this require a transactional log of book usage and who used them. Using a spreadsheet does not allow us to record these transactions or easily coordinate between two worksheets and archive this data. Some type of database is required for this information.

The discussion of checking in and checking out books requires another table schema. We need something simple to track the students.

  • Student id
  • Student first and last name
  • Homeroom teacher
  • Grade

With this information we can record when a student checks out a book by correlating a book to a student id. We can also let a parent look at the current books checked out by presenting the student id and searching for books checked out. The librarian can also tag a class of books for not to be checked out by grade levels. We might use either the series or rating elements in the book schema. For example, if a kindergartener wants to check out a book that is high school rated, the check out program will flag the process and require a verification that this child can check out this book.

We will also need a mechanism to record book status. This will require a specific schema element to track this.

  • book id
  • date checked out
  • student id

Some elements that are missing from this library schema are the cost of a book and the location of a book in the library. Since this library is a small library of a few thousand books with less than 20 book shelves, there is really no need to record this information. If we had multiple libraries or libraries in multiple rooms at the same site, we might want to record book location. If we were going to charge for books based on our cost of purchasing the book, we would want to record the cost of the books. Since our library is a volunteer library where the vast majority of books have been donated or purchased in bulk from Half Price Books, we don’t track the cost of each individual book. We do record how much has been spent on all of the books in the library. We have been collecting books for the past 8 months and have spent more on bookshelves than we have on books. We have just over 7000 books and have spent $500 on bookshelves (the cost of wood, nails, and screws) and $200 on books. The funding for these books came from a book fair where we got a percentage of profits from all books sold. During this book fair we had the teachers list books that they were interested in and books that they wanted in the library. The parents purchased a few hundred books and donated them to the library.

next topic on the library, use cases and user interface design. I will be using three different tools to design this library interface; Apps Express(HtmlDB), JDesigner, and NetBeans. The purpose of using three is to compare and contrast the three different tools for creating the same product. Once we have the design completed, we will look at what it takes to import our spreadsheet data into a database. The databases that we will evaluate are OracleXE, mySQL, and SQL Server Express. We will look at these three because they are all free databases.

299 thoughts on “library schema design”

  1. My wife and i were so thrilled when Emmanuel could carry out his investigations from the precious recommendations he was given from your blog. It is now and again perplexing to simply possibly be giving for free steps that many some others have been making money from. We really keep in mind we’ve got the website owner to be grateful to because of that. All the explanations you have made, the easy web site menu, the friendships your site assist to promote – it’s got all overwhelming, and it is letting our son and the family know that the topic is pleasurable, and that’s seriously vital. Thank you for all the pieces!

  2. Thank you so much for giving everyone an extremely memorable opportunity to read articles and blog posts from here. It really is so beneficial plus full of a great time for me personally and my office mates to visit the blog really three times in 7 days to study the newest items you have got. And of course, I am at all times amazed concerning the tremendous concepts served by you. Selected 2 points in this post are essentially the most beneficial I’ve ever had.

  3. Thanks so much for providing individuals with an exceptionally pleasant possiblity to check tips from here. It is usually very sweet and as well , jam-packed with a great time for me personally and my office acquaintances to visit your website at minimum three times in 7 days to study the newest guidance you will have. And lastly, I’m also at all times astounded with your remarkable principles served by you. Certain two facts in this article are clearly the most suitable we have ever had.

  4. Thank you a lot for giving everyone a very splendid opportunity to check tips from this website. It can be very useful and as well , jam-packed with a great time for me and my office peers to search the blog at the least thrice in 7 days to read the newest guides you have. Of course, I’m certainly fulfilled considering the unbelievable pointers you serve. Certain 2 points in this posting are essentially the most efficient I have ever had.

  5. I not to mention my pals appeared to be checking out the nice thoughts on the website while immediately I had an awful feeling I never thanked the site owner for those secrets. All of the young boys ended up for that reason stimulated to see all of them and have extremely been taking pleasure in those things. We appreciate you simply being well kind and also for utilizing this form of really good tips millions of individuals are really eager to understand about. My personal sincere apologies for not expressing appreciation to you earlier.

  6. I wish to show my appreciation for your kindness in support of all those that really want assistance with this important idea. Your personal commitment to getting the message all around had become wonderfully helpful and have in most cases permitted guys just like me to get to their dreams. Your new warm and helpful instruction can mean so much to me and a whole lot more to my office colleagues. With thanks; from everyone of us.

  7. Needed to send you the very small note to give thanks again regarding the pleasing concepts you have documented in this case. It’s tremendously generous with people like you giving publicly just what numerous people might have made available as an electronic book to get some money on their own, especially seeing that you might have tried it in case you considered necessary. These tricks additionally served like a fantastic way to fully grasp that other people online have a similar interest really like mine to know a lot more in respect of this matter. I think there are a lot more pleasurable situations in the future for individuals who look over your blog post.

  8. I wish to express my appreciation for your kindness in support of individuals who actually need assistance with that content. Your real commitment to passing the solution all over came to be quite useful and have in most cases enabled others just like me to realize their pursuits. Your important publication indicates a lot a person like me and still more to my colleagues. Thanks a lot; from all of us.

  9. I as well as my guys were found to be examining the excellent information from your website and then I got an awful suspicion I had not expressed respect to the web blog owner for those strategies. My guys became absolutely warmed to see them and already have actually been loving those things. We appreciate you indeed being indeed considerate and then for pick out some awesome resources most people are really desirous to be informed on. My personal sincere regret for not expressing appreciation to you sooner.

  10. Needed to post you this tiny remark so as to thank you very much yet again for these exceptional concepts you’ve shown on this page. This has been quite incredibly generous with people like you to provide unhampered precisely what a number of people could have marketed as an e book to help make some profit for themselves, most notably given that you could possibly have tried it in case you desired. The secrets additionally acted to become a good way to fully grasp someone else have a similar dreams the same as my own to know the truth more in regard to this matter. I think there are numerous more fun situations up front for people who view your blog post.

  11. I must show thanks to this writer for rescuing me from this type of instance. As a result of searching throughout the world wide web and finding ideas that were not powerful, I figured my entire life was gone. Living without the answers to the problems you have sorted out as a result of your good short post is a critical case, and ones that might have adversely damaged my career if I had not come across the blog. Your good talents and kindness in handling the whole thing was precious. I don’t know what I would have done if I had not come across such a point like this. I am able to at this moment relish my future. Thanks a lot very much for your high quality and sensible help. I won’t think twice to suggest your web site to any individual who needs guidance on this topic.

  12. I simply wanted to thank you so much once more. I am not sure the things I would’ve sorted out in the absence of the type of methods provided by you over such area. It previously was a very frightful crisis in my opinion, however , viewing this skilled form you managed it made me to weep over joy. I am just happy for your advice and thus pray you comprehend what a powerful job that you are providing teaching the mediocre ones thru your blog post. I am certain you have never met all of us.

  13. I simply wanted to write down a simple note to express gratitude to you for those unique secrets you are sharing on this website. My extended internet search has at the end been recognized with high-quality facts to exchange with my two friends. I would repeat that many of us website visitors actually are rather blessed to be in a really good community with very many brilliant professionals with good principles. I feel very much grateful to have encountered your entire web site and look forward to really more fun minutes reading here. Thanks again for a lot of things.

  14. I precisely wished to thank you so much yet again. I’m not certain the things that I would’ve followed without the type of creative ideas shown by you directly on such field. It actually was a very troublesome condition in my position, however , encountering the professional fashion you dealt with the issue took me to weep for gladness. Now i am happy for the information and as well , wish you really know what a great job you are carrying out teaching other individuals all through your blog post. Most likely you’ve never encountered all of us.

  15. I in addition to my guys have been checking the best pointers found on your web page and so immediately got a horrible suspicion I never expressed respect to the web site owner for those secrets. These young boys had been for this reason passionate to study them and now have surely been having fun with them. I appreciate you for genuinely really considerate and for deciding on varieties of exceptional topics most people are really needing to be informed on. My honest apologies for not saying thanks to you earlier.

  16. I am just commenting to let you be aware of of the really good experience my cousin’s girl experienced browsing your site. She came to find too many things, with the inclusion of what it’s like to possess a wonderful teaching heart to get a number of people clearly master several hard to do topics. You truly did more than our desires. Thank you for delivering such invaluable, dependable, revealing and as well as fun thoughts on that topic to Julie.

  17. I am only writing to make you know what a helpful discovery our daughter obtained reading your site. She came to understand a wide variety of details, including what it’s like to possess an excellent helping mood to get many more without hassle learn chosen specialized subject areas. You truly surpassed my expected results. Thank you for distributing such helpful, dependable, edifying not to mention easy thoughts on that topic to Tanya.

  18. I together with my friends came following the great tips and tricks found on your web site and then before long got a horrible suspicion I had not thanked the blog owner for those strategies. These young men had been consequently passionate to read all of them and now have actually been making the most of those things. Many thanks for truly being so kind and then for obtaining some essential topics most people are really desirous to know about. Our own honest regret for not saying thanks to you sooner.

  19. I would like to express my gratitude for your kindness for men and women who must have help on your area of interest. Your personal dedication to getting the message along appears to be really functional and has all the time made some individuals much like me to reach their desired goals. The valuable guideline signifies so much a person like me and even further to my peers. Thank you; from each one of us.

  20. I just wanted to make a brief message to be able to appreciate you for those pleasant information you are writing at this site. My incredibly long internet search has at the end been honored with reliable concept to share with my pals. I ‘d point out that most of us visitors are undeniably lucky to dwell in a magnificent website with so many perfect professionals with helpful tips. I feel really grateful to have discovered your weblog and look forward to many more entertaining times reading here. Thanks once again for a lot of things.

Leave a Reply

Your email address will not be published. Required fields are marked *