Library Resources Management System Database Design

Library Resources Management System Database Design

This article will provide you with the list of tables and entities for every table for the library resources management system. This is a hybrid library system for it includes the processing of book purchase, delivery details and as well as the claiming of books ordered by the students and faculty.

Download .sql file

tblbook – this table will store the information of the book as presented below and it has 8 attributes.

• id – this is the primary key of the table, primary key refers to the unique key which has no duplicate value.
• accessionnumber – An accession number is a sequential number given to each new book, magazine subscription, or recording as it is entered in the catalog of a library. If an item is removed from the collection, its number is usually not reused for new items. (https://www.kaggle.com/c/harvard-business-review-vision-statement-prospect/discussion/2399)

This value is unique for every book in the library even if the book are identical

• bookname – the title of the book will store under the field name bookname.
• authors – the authors of the book will be stored in this column.
• isbn – The International Standard Book Number is a numeric commercial book identifier which is intended to be unique. Publishers purchase ISBNs from an affiliate of the International ISBN Agency. An ISBN is assigned to each separate edition and variation of a publication. (https://en.wikipedia.org/wiki/International_Standard_Book_Number)
• price – price column refers to the amount of the book from the supplier where it was purchased.
• initialqty – for inventory purposes the initialqty is the column that stores the number of copies of the book.
• gradelevel – this column represents the grade level (elementary, highschool, college) of who will be prioritized in the borrowing of a certain book. Example, if the book was bought for the need of grade 6 students then the grade 6 students will be on the priority list, but anyone can borrow the book.

Create SQL Statement – the statement below is used to create the tblbook, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblbook` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`accessionnumber` varchar(11) NOT NULL,
`bookname` varchar(100) NOT NULL,
`authors` varchar(100) NOT NULL,
`isbn` varchar(20) NOT NULL,
`price` double NOT NULL,
`initialqty` int(11) NOT NULL,
`gradelevel` varchar(15) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblborrowed – list of books borrowed by the students, faculty and staff of the school will be recorded and archived in this table. The table has 8 columns.

• id – this is the primary key of the table.
• facultyid – foreign key that links to the primary key of faculty table (tblfaculty).
• bookid – foreign key that links to the primary key of book table (tblbook).
• qty – this column refers to the quantity of books borrowed.
• daterecorded – date of transaction are stored in this column.
• processedby – this column refers to the user who processed the transaction. This is a foreign key that connects to the user table (tbluser).
• status – this field represents if the book has been returned or not. The values stored for this column is 0 and 1, 0 if returned, 1 for unreturned books.
• duedate – this is the date that the book must be returned by the borrower.

Create SQL Statement – the statement below is used to create the tblborrowed, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblborrowed` (
`id` int(11) NOT NULL,
`facultyid` int(11) NOT NULL,
`bookid` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`daterecorded` varchar(15) NOT NULL,
`processedby` int(11) NOT NULL,
`mstatus` varchar(11) NOT NULL,
`duedate` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblclaim – claim table will store the record on the books claimed by the students and faculty.

• id – primary key of the table
• studentid – foreign key of the table that connects to the student table (tblstudent).
• daterecorded – this column represents to the date the book was claimed by the student.
• total – total is the column for the total amount of claimed books.
• processedby – this column refers to the user who processed the transaction. This is a foreign key that connects to the user table (tbluser).

Create SQL Statement – the statement below is used to create the tblclaim, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblclaim` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`studentid` int(11) NOT NULL,
`daterecorded` varchar(15) NOT NULL,
`total` double NOT NULL,
`processedby` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblclaimdetail – this table is connected to the tblclaim table, since the student can claim more than one book, this table was created for that purpose.

• claimid – this column contains the value of the id of the tblclaim
• bookid – this is the foreign key that links to the book table (tblbook). It represents the book claimed by the student.

Create SQL Statement – the statement below is used to create the tblclaimdetail, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblclaimdetail` (
`claimid` int(11) NOT NULL,
`bookid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblcompanysetup – this the table is used to store the information of the company or school. The values of this table will be used in every reports of the system.

• id – primary key of the table.
• companyname – name of the school or company.
• address – school address.
• contact – contact information of the school.

Create SQL Statement – the statement below is used to create the tblcompanysetup, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblcompanysetup` (
`id` int(11) NOT NULL,
`companyname` varchar(50) NOT NULL,
`address` varchar(100) NOT NULL,
`contact` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblfaculty – personal information of the faculty will be stored in the tblfaculty table and it has 6 entities.

• id – primary key of the table.
• facultyid – this is the id number provided the school for every faculty.
• lastname – last name of the faculty.
• firstname – first name of the faculty.
• middlename – middle name of the faculty.
• contact – contact information of the faculty.

Create SQL Statement – the statement below is used to create the tblfaculty, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblfaculty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`facultyid` varchar(15) NOT NULL,
`lastname` varchar(20) NOT NULL,
`firstname` varchar(20) NOT NULL,
`middlename` varchar(20) NOT NULL,
`contact` varchar(15) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblpurchaseorder – list of books that the library needs based on the request of the students and faculty will be recorded in the tblpurchaseorder and tblpurchaseorderdetail table.

• id – the primary key of the table.
• pono – this column is the purchase order number, it is a system generated value.
• daterecorded – the date the purchase order was created and printed.
• supplier – the name of the supplier where the books will be ordered.
• processedby – this column refers to the user who processed the transaction. This is a foreign key that connects to the user table (tbluser).

Create SQL Statement – the statement below is used to create the tblpurchaseorder, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblpurchaseorder` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pono` varchar(15) NOT NULL,
`daterecorded` date NOT NULL,
`supplier` varchar(25) NOT NULL,
`processedby` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblpurchaseorderdetail – details of the purchase order are stored in this table.

• poid – this column refers to the primary key of the tblpurchaseorder which is the id column.
• bookid – foreign key that links to the primary key of book table (tblbook).
• qty – quantity of books to be ordered
• amount – the amount of book
• total – total is equals to the qty * amount. This is optional, meaning you can omit this value since it is a derived attribute.

Create SQL Statement – the statement below is used to create the tblpurchaseorderdetail, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblpurchaseorderdetail` (
`poid` int(11) NOT NULL,
`bookid` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`amount` float NOT NULL,
`total` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblreceivebooks – list of books delivered by the supplier will be stored in the tblreceivebooks and tblreceivedetails.

• id – primary key of the table
• daterecorded – date of delivery
• supplier – name of supplier
• poid – reference id based on the purchase record. This is a foreign key that connects to the purchase table (tblpurchase)
• processedby – this column refers to the user who processed the transaction. This is a foreign key that connects to the user table (tbluser).

Create SQL Statement – the statement below is used to create the tblreceivebooks, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblreceivebooks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`receiveno` varchar(11) NOT NULL,
`daterecorded` date NOT NULL,
`supplier` varchar(30) NOT NULL,
`poid` int(11) NOT NULL,
`processedby` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

tblreceivedetails – details of the delivery will be stored in this table.

• receivedid – this value is the same to the value of the primary key of tblreceivebooks.
• bookid – foreign key that links to the primary key of book table (tblbook).
• qty – quantity of books received.
• amount – the amount of book
• total – total is equals to the qty * amount. This is optional, meaning you can omit this value since it is a derived attribute.

Create SQL Statement – the statement below is used to create the tblreceivedetails, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblreceivedetails` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`receivedid` int(11) NOT NULL,
`bookid` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`bamount` varchar(11) NOT NULL,
`btotal` varchar(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblreturn – there are cases that the book will be returned by the student or faculty, and to cater those scenario the tblreturn table will be created.

• id – primary key of the table
• facultyid – foreign key of the table that connects to the faculty table (tblfaculty).
• bookid – foreign key that links to the primary key of book table (tblbook). This is the book that will be returned.
• qty – number of books to be returned.
• datereturned – date of transaction.
• Processedby – this column refers to the user who processed the transaction. This is a foreign key that connects to the user table (tbluser).

Create SQL Statement – the statement below is used to create the tblreturn, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblreturn` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`facultyid` int(11) NOT NULL,
`bookid` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`datereturned` date NOT NULL,
`processedby` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblreturntosupplier – if the books delivered by the supplier is not the book needed by the school or it is not the book that reflects to the purchase order then the books will be returned to the supplier. Those records and transactions are also being recorded in the system.

• id – primary key of the table.
• bookid – foreign key that links to the primary key of book table (tblbook). This is the book that will be returned.
• qty – quantity of books to be returned.
• supplier – name of supplier
• datereturned – date of transaction.
• Processedby – this column refers to the user who processed the transaction. This is a foreign key that connects to the user table (tbluser).

Create SQL Statement – the statement below is used to create the tblreturntosupplier, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblreturntosupplier` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bookid` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`supplier` varchar(30) NOT NULL,
`daterecorded` date NOT NULL,
`processedby` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblstudent –  information of the students will be stored in the tblstudent table.

• id – primary key of the table
• studentidno – student id number provided by the school.
• lastname – last name of the student.
• firstname – first name of the student.
• middlename – middle name of the student.
• contact – contact information of the student,
• gradelevel – grade level of the student.

Create SQL Statement – the statement below is used to create the tblstudent, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblstudent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`studentidno` varchar(11) NOT NULL,
`lastname` varchar(20) NOT NULL,
`firstname` varchar(20) NOT NULL,
`middlename` varchar(20) NOT NULL,
`contact` varchar(15) NOT NULL,
`gradelevel` varchar(15) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbltempborrow – temporary table that stores the borrowed information. Information stored in this table will be passed to the tblborrow table and records from  this table will be automatically deleted that will be used in the next transaction.

• id – primary key
• facultyid – foreign key that links to the primary key of faculty table (tblfaculty).
• bookid – foreign key that links to the primary key of book table (tblbook).
• qty – quantity of books to be borrowed.
• daterecorded – date of transaction.
• duedate – date of return.

Create SQL Statement – the statement below is used to create the tbltempborrow, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tbltempborrow` (
`id` int(11) NOT NULL,
`facultyid` int(11) NOT NULL,
`bookid` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`daterecorded` date NOT NULL,
`duedate` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbltempreceive – – temporary table that stores the delivery of books information. Information stored in this table will be passed to the tblreceivebooks and tblreceivedetails table and records from this table will be automatically deleted that will be used in the next transaction.

• poid – purchase order id
• bookid – foreign key that links to the primary key of book table (tblbook).
• qty – quantity of books received.
• amount – the amount of book
• total – total is equals to the qty * amount. This is optional, meaning you can omit this value since it is a derived attribute.

Create SQL Statement – the statement below is used to create the tbltempreceive, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tbltempreceive` (
`poid` int(11) NOT NULL,
`bookid` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`amount` float NOT NULL,
`total` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblusers – list of users that can access the system are stored in this table.

• id – primary key of the table.
• fullname – full name of the user/staff.
• contact – contact information.
• address – address of the user/staff.
• myusername – username of the user.
• mypassword – password of the user.
• usercategory – the system has three types of user accounts; administrator/librarian, staff, working student.

Create SQL Statement – the statement below is used to create the tblusers, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblusers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fullname` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`address` varchar(50) NOT NULL,
`myusername` varchar(15) NOT NULL,
`mypassword` varchar(15) NOT NULL,
`usercategory` varchar(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

You may visit our facebook page for more information, inquiries and comments.

Hire our team to do the project.