Database schema

[anselal]

Hi there,

some correction in the database schema. You should not add (repeat) the fields bookName, memberName, phone again in other tables. Since you already have added the foreign key you can just join the tables you want.

[anselal]

Besides that, each table should have a primary key

[MalayAgr]

Hey, thanks for the feedback!

I'll definitely look into this and update accordingly.

[anselal]

I will paste an example schema here within the hour

[anselal]

the DB Schema should look something like the following. This is not the ideal schema but is as close as it gets to your schema.

CHANGELOG:

• I added primary keys (AUTO_INCREMENT) to the tables Issues, Purchases and Returns
• I added a table for PurchaseTypes You should populate dropdown menu in the purchase form https://github.com/MalayAgarwal-Lee/Library-Management-System/blob/d64a152cef3fcdf6b1c10b03e4680d436602eecf/src/PurchaseBook.java#L183 from all the values in this table.
• Since I added PRIMARY_KEYS for some tables, some forms don't work anymore eg. Add Purchase

-- Dumping database structure for library
CREATE DATABASE IF NOT EXISTS `library` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `library`;

-- Dumping structure for table library.books
CREATE TABLE IF NOT EXISTS `books` (
  `bookNo` int(11) NOT NULL AUTO_INCREMENT,
  `bookName` varchar(100) NOT NULL,
  `author` varchar(100) NOT NULL,
  `publisher` varchar(100) NOT NULL,
  `pages` int(11) NOT NULL,
  `stock` int(11) NOT NULL,
  `balance` float NOT NULL,
  PRIMARY KEY (`bookNo`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- Dumping data for table library.books: ~1 rows (approximately)
/*!40000 ALTER TABLE `books` DISABLE KEYS */;
/*!40000 ALTER TABLE `books` ENABLE KEYS */;

-- Dumping structure for table library.issues
CREATE TABLE IF NOT EXISTS `issues` (
  `issueNo` int(11) NOT NULL AUTO_INCREMENT,
  `bookNo` int(11) NOT NULL,
  `bookName` varchar(100) NOT NULL,
  `memberNo` int(11) NOT NULL,
  `memberName` varchar(100) NOT NULL,
  `phone` varchar(100) NOT NULL,
  `noOfcopies` int(11) NOT NULL,
  `dateOfissue` datetime NOT NULL,
  `leftCopies` int(11) NOT NULL,
  PRIMARY KEY (`issueNo`),
  KEY `FK_issues_books` (`bookNo`),
  KEY `FK_issues_members` (`memberNo`),
  CONSTRAINT `FK_issues_books` FOREIGN KEY (`bookNo`) REFERENCES `books` (`bookNo`),
  CONSTRAINT `FK_issues_members` FOREIGN KEY (`memberNo`) REFERENCES `members` (`memberNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table library.issues: ~0 rows (approximately)
/*!40000 ALTER TABLE `issues` DISABLE KEYS */;
/*!40000 ALTER TABLE `issues` ENABLE KEYS */;

-- Dumping structure for table library.members
CREATE TABLE IF NOT EXISTS `members` (
  `memberNo` int(11) NOT NULL AUTO_INCREMENT,
  `memberName` varchar(100) NOT NULL,
  `address` varchar(100) NOT NULL,
  `dob` date NOT NULL,
  `phone` varchar(100) NOT NULL,
  PRIMARY KEY (`memberNo`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- Dumping data for table library.members: ~1 rows (approximately)
/*!40000 ALTER TABLE `members` DISABLE KEYS */;
/*!40000 ALTER TABLE `members` ENABLE KEYS */;

-- Dumping structure for table library.purchases
CREATE TABLE IF NOT EXISTS `purchases` (
  `purchaseNo` int(11) NOT NULL AUTO_INCREMENT,
  `bookNo` int(11) NOT NULL,
  `bookName` varchar(100) NOT NULL,
  `noOfCopies` int(11) NOT NULL,
  `dateOfPurchase` datetime NOT NULL,
  `typeNo` int(11) NOT NULL,
  `costPerCopy` float NOT NULL,
  `totalCost` float NOT NULL,
  PRIMARY KEY (`purchaseNo`),
  KEY `FK_purchases_books` (`bookNo`),
  KEY `FK_purchases_purchasetype` (`typeNo`),
  CONSTRAINT `FK_purchases_purchasetype` FOREIGN KEY (`typeNo`) REFERENCES `purchasetype` (`typeNo`),
  CONSTRAINT `FK_purchases_books` FOREIGN KEY (`bookNo`) REFERENCES `books` (`bookNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table library.purchases: ~0 rows (approximately)
/*!40000 ALTER TABLE `purchases` DISABLE KEYS */;
/*!40000 ALTER TABLE `purchases` ENABLE KEYS */;

-- Dumping structure for table library.purchasetype
CREATE TABLE IF NOT EXISTS `purchasetype` (
  `typeNo` int(11) NOT NULL AUTO_INCREMENT,
  `typeName` varchar(100) NOT NULL,
  PRIMARY KEY (`typeNo`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- Dumping data for table library.purchasetype: ~1 rows (approximately)
/*!40000 ALTER TABLE `purchasetype` DISABLE KEYS */;
REPLACE INTO `purchasetype` (`typeNo`, `typeName`) VALUES
  (1, 'Online'),
  (2, 'Wholesale');
/*!40000 ALTER TABLE `purchasetype` ENABLE KEYS */;

-- Dumping structure for table library.returns
CREATE TABLE IF NOT EXISTS `returns` (
  `returnNo` int(11) NOT NULL AUTO_INCREMENT,
  `bookNo` int(11) NOT NULL,
  `bookName` varchar(100) NOT NULL,
  `memberNo` int(11) NOT NULL,
  `memberName` varchar(100) NOT NULL,
  `phone` varchar(100) NOT NULL,
  `noOfcopies` int(11) NOT NULL,
  `dateOfReturn` datetime NOT NULL,
  `leftCopies` int(11) NOT NULL,
  PRIMARY KEY (`returnNo`),
  KEY `FK_returns_books` (`bookNo`),
  KEY `FK_returns_members` (`memberNo`),
  CONSTRAINT `FK_returns_books` FOREIGN KEY (`bookNo`) REFERENCES `books` (`bookNo`),
  CONSTRAINT `FK_returns_members` FOREIGN KEY (`memberNo`) REFERENCES `members` (`memberNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

You can also use add this schema to your repo to help people create the tables needed for your application ;)

[MalayAgr]

Thanks for the great suggestions, @anselal. I'll refine this application soon. A little caught up with examinations right now, so this will have to wait. I'll get back to you once I add all this.

[anselal]

No problem. Take your time !!!

[anselal]

PS: Great article at realpython.com !!!!

[MalayAgr]

Thank you very much! Really glad that you enjoyed it. 😄