I'm a bit new to coding in general and seem to be struggling to wrap my mind around how to store data effectively for my application. (I'm attempting this in Laravel, with mySql)
I realise this question might lean towards being opinion-specific, but I am really looking for obvious pointers on false assumptions I have made, or a nudge in the direction of best-practices.
I will be tracking a number of messages, as if they were credits in a bulk email management system. One message-batch could use an undetermined number of credits to fire off a full batch of messages.
Groups of users will be able to send messages if they have credits to do so, One solution I have thought of is to have a table: id, user_group_id, debt/credit, reference_code - Where user_group_id would be linked to the group to which the user belongs, the debit/credit column could hold a positive or negative number (of a message related transaction), and the reference_code would track the type of transaction. Debits/Credit transactions would come about where the user_group account received new credits (purchased block of new credits), or in the case of a debits example, where batches of messages had been sent.
All this background leads to my question .. I still don't hold a single value for the available number of credits a user_group has. Before being able to send a new batch, should I be running a database query each time that sums all the "accounting" of positive and negative transactions to determine whether a user is "in the black" to be able to send further message batches, or should I have an additional table and keeps the result of their available credits total separately?
If I do store the total-available-credits value by itself, when should this single value be updated, at the end of every message-related task my application performs ? *user adds new credits, update total - user sends batch, update total.. etc.
" should I be running a database query each time that sums all the "accounting" of positive and negative transactions to determine whether a user is "in the black" to be able to send further message batches "
YES
This is an interesting question. Opinionated as you pointed out but nonetheless interesting. Database design is not my strong suit but here is how I would do it.
First, ensure integrity with INNODB tables and foreign key constraints. I would keep the total remaining credits each user group has in the user group table. You cold then create a transaction table with a Transaction ID, the User Group ID, and the credits used for that transaction so that you could follow each user group's transaction history.
Just a thought. Like I said, I'm by no means an expert. It may be useful however, to have a log of some sort so that you could verify transactions later in case of a credit discrepancy. You could always use this later to recalculate the remaining credits to ensure the numbers align.
Since these transactions may be important for credit/billing purposes, you may also want to turn off MySQL's auto commit and use the commit and rollback features to ensure your data stays in tact in case of an error.