MYSQL COMPLEXITY

[Olumide]

total_fee_payable is still not calculating well but calculated_total_fee calculated well as required.
I want this calculated as: 

total_fee_payable = (fee_amount + total_fee_from_items) - (total_fee_from_items + discount_amount)

I have tried many approach but not working, it is not adding the amounts in the items_table to the total_fee_payable in the invoices table.

From the sample query below:

From items_table: invoice_id 118 should be added to the invoices id total_fee_payable 

 

 

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `semester_id` int(11) NOT NULL,
  `student_name` varchar(255) NOT NULL,
  `current_class_id` varchar(255) DEFAULT NULL,
  `fee_description` varchar(255) NOT NULL,
  `fee_amount` decimal(10,2) NOT NULL,
  `less_fee_description` varchar(255) DEFAULT NULL,
  `less_fee_amount` decimal(10,2) DEFAULT NULL,
  `discount_amount` decimal(10,2) DEFAULT NULL,
  `total_fee_payable` decimal(10,2) NOT NULL,
  `invoice_number` varchar(50) NOT NULL,
  `status` enum('PAID','PENDING') DEFAULT 'PENDING',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `invoices`
--

INSERT INTO `invoices` (`id`, `student_id`, `semester_id`, `student_name`, `current_class_id`, `fee_description`, `fee_amount`, `less_fee_description`, `less_fee_amount`, `discount_amount`, `total_fee_payable`, `invoice_number`, `status`, `created_at`, `updated_at`) VALUES

(118, 678, 16, 'AU', 'Year 11', 'school fee ', '50000.00', 'hg', '1000.00', '1000.00', '48000.00', 'B/2024/67982', 'PENDING', '2024-02-03 00:12:06', '2024-02-03 00:12:06');

--
-- Triggers `invoices`
--
DELIMITER $$
CREATE TRIGGER `calculate_total_fee_payable_update` BEFORE UPDATE ON `invoices` FOR EACH ROW BEGIN
    SET NEW.total_fee_payable = (
        SELECT COALESCE(SUM(fee_amount), 0) 
        FROM items_table 
        WHERE invoice_id = NEW.id
    ) + NEW.fee_amount - COALESCE((NEW.less_fee_amount + NEW.discount_amount), 0);
END
$$
DELIMITER ;


ALTER TABLE `invoices`
  ADD PRIMARY KEY (`id`),
  ADD KEY `semester_id` (`semester_id`),
  ADD KEY `fk_invoices_student` (`student_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `invoices`
--
ALTER TABLE `invoices`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=119;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `invoices`
--
ALTER TABLE `invoices`
  ADD CONSTRAINT `fk_invoices_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`),
  ADD CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`semester_id`) REFERENCES `semester` (`id`);
COMMIT;

 

CREATE TABLE `items_table` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `fee_description` varchar(255) NOT NULL,
  `fee_amount` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


INSERT INTO `items_table` (`id`, `invoice_id`, `fee_description`, `fee_amount`) VALUES
(16, 118, 'sports', '5000.00'),
(17, 118, 'club', '5000.00');


ALTER TABLE `items_table`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_items_invoice` (`invoice_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `items_table`
--
ALTER TABLE `items_table`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `items_table`
--
ALTER TABLE `items_table`
  ADD CONSTRAINT `fk_items_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`);
COMMIT;

 

 

Edited February 3 by Olumide
omission

[Barand]

12 hours ago, Olumide said:

it is not adding the amounts in the items_table to the total_fee_payable in the invoices table.

That's good. You shouldn't be doing that. Don't store derived data.

The invoice record should essentially contain the header information for a printed invoice (customer, invoice number, invoice date etc.). All the amounts should be in the invoice items table.

The invoice total is the sum of the items in the invoices (Credit items would have a negative amount.) Customer discount would then be appplied.

[Olumide]

41 minutes ago, Barand said:

That's good. You shouldn't be doing that. Don't store derived data.

The invoice record should essentially contain the header information for a printed invoice (customer, invoice number, invoice date etc.). All the amounts should be in the invoice items table.

The invoice total is the sum of the items in the invoices (Credit items would have a negative amount.) Customer discount would then be appplied.

Thanks @BarandCan you please give me more hints on this?

[mac_gyver]

this is at least the 3rd time someone has stated in your threads to not store derived values. you just calculate them when needed. and if you store the raw data properly, it is a simple and fast query to do so.

you should (and probably do) have a student table, which holds the unique/one-time student data. this defines the student_ids. any data related to a student should use the student_id. you should not have any other values from the student table, such as the student_name, in any other table.

you should (and probably do) have a semester table, which holds the unique/one-time semester data. this defines the semester_ids.

you should (and probably do) have a class table, which holds the unique/one-time class data. this defines the class_ids.

you should have a register table, which i suspect is what the invoices table is trying to be, that holds a row for each class, for each semester, for each student. this table defines the class_semester_student_ids that should be used when storing related data, such as invoices, assignments, exams, ...

to keep track of the invoice data, per my reply in your previous most recent thread - "you should have a 'fee' table that holds the different type of fees/discounts. you would then have a select/option menu to select the fee type, which submits the fee_id, and the fee amount." you would insert a row for each fee/discount for each class_semester_student_id into an invoice_item(s) table (currently named items_table.) to get the current amount for any or all fee type(s) for any or all class(es), semester(s), or student(s), you would simply SUM() the +/- amounts for the records matching the WHERE term that you build.

 

 

[Olumide]

4 hours ago, mac_gyver said:

this is at least the 3rd time someone has stated in your threads to not store derived values. you just calculate them when needed. and if you store the raw data properly, it is a simple and fast query to do so.

you should (and probably do) have a student table, which holds the unique/one-time student data. this defines the student_ids. any data related to a student should use the student_id. you should not have any other values from the student table, such as the student_name, in any other table.

you should (and probably do) have a semester table, which holds the unique/one-time semester data. this defines the semester_ids.

you should (and probably do) have a class table, which holds the unique/one-time class data. this defines the class_ids.

you should have a register table, which i suspect is what the invoices table is trying to be, that holds a row for each class, for each semester, for each student. this table defines the class_semester_student_ids that should be used when storing related data, such as invoices, assignments, exams, ...

to keep track of the invoice data, per my reply in your previous most recent thread - "you should have a 'fee' table that holds the different type of fees/discounts. you would then have a select/option menu to select the fee type, which submits the fee_id, and the fee amount." you would insert a row for each fee/discount for each class_semester_student_id into an invoice_item(s) table (currently named items_table.) to get the current amount for any or all fee type(s) for any or all class(es), semester(s), or student(s), you would simply SUM() the +/- amounts for the records matching the WHERE term that you build.

 

 

The fee is not constant, it do change, and for instance, a student who lives in hostel (Boarder) might decide he want to be coming from home as Day student, and a Day student can even decide he want to stay in hostel for a week maybe for a purpose known to him, and this do cause confusion if there is fixed price. 

Also, some parents might decide they want to pay less on any of the fee either IGCSE, Sports or anything. That is why I think entering the fee manually could be okay to handle such scenario.

Some students are on scholarship where tuition will be free, but they will pay for other expenses and some on free feeding, and so on. And School fee may change anytime.

[mac_gyver]

nothing i wrote concerns a fixed amount for each fee type. the fee type is the name/meaning of the fee and by submitting the correspond fee_id and storing that, you are normalizing the data, which will result in the least amount of data storage and the fastest queries. the amount field should actually get populated with the current default amount for the selected fee type (i would use data- attributes and some javascript), for those cases where the standard fee will be used.

[Olumide]

1 hour ago, mac_gyver said:

nothing i wrote concerns a fixed amount for each fee type. the fee type is the name/meaning of the fee and by submitting the correspond fee_id and storing that, you are normalizing the data, which will result in the least amount of data storage and the fastest queries. the amount field should actually get populated with the current default amount for the selected fee type (i would use data- attributes and some javascript), for those cases where the standard fee will be used.

I understand your point, for instance storing names again in the invoice table when the names are available in the student table is like exhausting more spaces and I don't think it can last if the storage space is not much, it can crash. You and @Barand have contributed tremendously. I am not yet an expert like you all, but I believe in consistent practicing. 

[gizmola]

52 minutes ago, Olumide said:

I understand your point, for instance storing names again in the invoice table when the names are available in the student table is like exhausting more spaces and I don't think it can last if the storage space is not much, it can crash. You and @Barand have contributed tremendously. I am not yet an expert like you all, but I believe in consistent practicing. 

 

Glad you have taken advice.  In fact, what they have been warning you against has a technical name in the world of database design, and that is "de-normalization".  When you redundantly store data, you are moving away from the practice of "normalization" which is the correct design.