Sql - Invoice Payment System

Question

please I need your help with creating a payment system. here is what I intend the payment setup to accomplish:

Products can be purchased by paying either the full amount specified on the product or splitting the payment into two installments.
The system should be able to keep track of user's amount due
Discounts can be applied to products by admin

This is what I have been able to come up with

DROP TABLE IF EXISTS `invoice`;
CREATE TABLE IF NOT EXISTS `invoice` (
  `invoiceId` int(11) NOT NULL AUTO_INCREMENT,
  `description` text NOT NULL,
  `amount` int(255) NOT NULL,
  `isPaid` tinyint(1) NOT NULL DEFAULT '0',
  `creation_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`invoiceId`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `invoice`
--
INSERT INTO `invoice` (`invoiceId`, `description`, `amount`, `isPaid`, `creation_timestamp`) VALUES
(1, 'Product Purchase', 6000, 0, '2018-08-09 22:11:10'),
(2, 'Product Purchase', 10000, 0, '2018-08-09 22:11:10'),
(3, 'Product Purchase', 15000, 0, '2018-08-09 22:31:15');

-- --------------------------------------------------------

--
-- Table structure for table `payment`
--

DROP TABLE IF EXISTS `payment`;
CREATE TABLE IF NOT EXISTS `payment` (
  `paymentId` int(11) NOT NULL AUTO_INCREMENT,
  `invoiceId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `paymentDate` timestamp NOT NULL,
  `amount` int(11) NOT NULL,
  PRIMARY KEY (`paymentId`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `payment`
--

INSERT INTO `payment` (`paymentId`, `invoiceId`, `userId`, `product_id`, `paymentDate`, `amount`) VALUES
(1, 1, 1, 1, '2018-08-07 23:00:00', 2500);

-- --------------------------------------------------------

--
-- Table structure for table `purchase`
--

DROP TABLE IF EXISTS `purchase`;
CREATE TABLE IF NOT EXISTS `purchase` (
  `purchaseId` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `purchase_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `end_date` date NOT NULL,
  `invoiceId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  PRIMARY KEY (`purchaseId`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `purchase`
--

INSERT INTO `purchase` (`purchaseId`, `product_id`, `purchase_date`, `end_date`, `invoiceId`, `userId`) VALUES
(1, 1, '2018-08-09 22:26:16', '2018-08-31', 1, 1),
(2, 4, '2018-08-09 22:26:16', '2019-04-30', 2, 2);
COMMIT;

with this, will I be able to perform what the schema is meant for?

Answer 1 · 2018-08-10T11:56:08Z

August 10, 2018 11:56am

Hi John

When you say the "system should keep track of user's amount due", this is perfectly possible when a new payment is entered, as you can either pass (or find out) the total paid to date from the payment table against an invoice_id and if it totals the amount in the invoice table then change the isPaid to 1.

If you want the database (presumably MySQL) to update the isPaid field automatically then you would need to look into Triggers.

Hope that helps

Niki

Welcome to the Treehouse Community

Looking to learn something new?

John Shawn

John Shawn

Sql - Invoice Payment System

1 Answer

Niki Molnar

Niki Molnar