proud Posted January 3, 2010 Share Posted January 3, 2010 Hello everyone, I want to code a Books Library management system using php and mysql, this book library should allow me to: 1- Search for books by name, category etc.. 2- Add new books to the library 3- Record books state, whether they are: present on the library or rented by a student 4- Record the date the book was out of the library and the date it was returned, 5- Finally, it should give a notification if a certain book was not returned for more than 2 days How many tables should I need to achieve the above goals? and how should the table structures and fields look like? Is there any ideas? Any help would be appreciated indeed.. Quote Link to comment Share on other sites More sharing options...
ignace Posted January 3, 2010 Share Posted January 3, 2010 I estimate 10 tables. And the table structure should have primary keys and foreign keys, the fields should have a proper datatype. Quote Link to comment Share on other sites More sharing options...
proud Posted January 3, 2010 Author Share Posted January 3, 2010 can you be more specific? and what are the data fields for those tables? Don't you think that 10 tables is too much? I can imagine 3 tables: Admin, Books, and Students/Users what should the rest 7 tables be? Quote Link to comment Share on other sites More sharing options...
teamatomic Posted January 4, 2010 Share Posted January 4, 2010 Books: ISBN dewey type //public or reference format // HC-PB-periodical author title desc num_copies check_out_date status //in/out Users: ID name email overdue_ban access_level whatever else you want to keep Now you will also need to make a new table every day like 01012010 or whatever you decide for the date format. This is to keep track of checked out and returned books and so you have a record of the in/out. You could just keep track in the books table but then you would have to loop through all your books to send notices and track overdues. You could alternately just have a "out" table and keep a daily copy of it for records/log purposes. It should be like this out or date or whatever ISBN ID / user who has book out due_date I wont build your SQL file for you but will tell you to get a forum script and look at the SQL file to give you an idea of what you need to do. This is because if you are going to make a library catalog you will have to know this stuff yourself. Here is a good start and inline with what you want to do. http://www.sitepoint.com/blogs/2009/03/12/mysql-foreign-keys-quicker-database-development/ I would suggest. Most people would tell you to use the user ID for a primary. I would suggest for this instance you look at using the ISBN. You should also want to consider how you handle periodicals. You might want to make a table for them instead of keeping them under books. Just one more thing, if you have texts that have supplements you should decide if you want them stored. I would suggest you consider keeping them in their own table and foreign them to the book. HTH Teamatomic Quote Link to comment Share on other sites More sharing options...
proud Posted January 4, 2010 Author Share Posted January 4, 2010 Hello teamatomic, this post is helpful and shows me from where to start, Thank you very much indeed.. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.