I am creating the application for the weekly EMI collection.

Some customers pay the amount partially and my application accepts only the full payment because we already created the EMI date.

We need to redesign the DB and it will accept partial payment also.

my db table:

CREATE TABLE `emi` (
  `emiId` bigint(20) NOT NULL AUTO_INCREMENT,
  `loanId` bigint(20) NOT NULL,
  `customerId` int(11) NOT NULL,
  `loanMode` int(11) NOT NULL,
  `loanType` int(11) NOT NULL,
  `emiNo` int(11) NOT NULL,
  `emiDate` date NOT NULL,
  `emiAmount` decimal(10,2) NOT NULL,
  `totalAmount` decimal(10,2) DEFAULT NULL,
  `finalAmount` decimal(10,2) DEFAULT NULL,
  `paidAmount` decimal(10,2) DEFAULT NULL,
  `paidDate` date DEFAULT NULL,
  `paymentMode` int(11) DEFAULT NULL,
  `status` int(11) NOT NULL,
  `collectedBy` int(11) DEFAULT NULL,
  `transactionId` bigint(20) DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `createdBy` int(11) NOT NULL,
  `createdDate` datetime NOT NULL DEFAULT current_timestamp(),
  `updatedBy` int(11) DEFAULT NULL,
  `updatedDate` datetime DEFAULT NULL,
  PRIMARY KEY (`emiId`),
  KEY `loanId` (`loanId`),
  KEY `collectedBy` (`collectedBy`),
  CONSTRAINT `emi_ibfk_1` FOREIGN KEY (`loanId`) REFERENCES `loan` (`loanId`),
  CONSTRAINT `emi_ibfk_2` FOREIGN KEY (`collectedBy`) REFERENCES `users` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=513 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

I need the Emi table exactly like in the video. please check the YouTube video. (5:20 to 6:30)