PHP/DB pattern question

Question

I have an app that works with an idea of "redemption codes" (schema: ID, NAME, USES, CODE). And example would be "32, Stack Overflow, 75, 75%67-15hyh"

So this code is given to the SO community, let's say, and it has 75 redemptions. You redeem it by entering some shipping info and the code. When entered, this check is preformed:

if (code exists){
    if (count_entries_where_code=$code < $uses_set_at_creation){
        //enter information into DB for processing
    {
    //echo "sorry, not a real code"
}

So the number of total uses is hardcoded, but the current # of redemptions is generated with a SQL query (count_results from entry_data WHERE code=$code). This part works fine, but here is the question:

At the view page where I manage the codes, I have the basic setup (in pseudo PHP, with the real code separated into an MVC setup):

$results = "SELECT * FROM codes";
foreach ($result as $code){
    echo $code->code;
    echo $code->name;
    //etc. It's actually all in a nice HTML table.
}

So I want to have a column listing "# of uses remaining on code". Should something like this be stored in the DB, and drawn out that way? It would be easier to generate with the foreach loop, but I don't usually prefer to store "generated" statistics like that. Is there a clever way to get those results onto the correct rows of the table created with the foreach loop?

(I'm fine with code so I don't need a working/great syntax example, just an explanation of a pattern that might fit this problem, and maybe a discussion of a common design for something like this. Am I right to avoid storing generate-able data like # of uses left? etc.)

Answer 1

When you run your query to get the codes for the page add a subquery to get the number of used codes from the entry_data table.

select codes.id, codes.name, codes.uses, codes.code (select count(code) from entry_data where entry_data.code=codes.code ) as used_codes

Id use code_id as a foreign key and not code.

This is all assuming i'm reading your problem correctly

Answer 2

Am I right to avoid storing generate-able data like # of uses left?

Yes, you are correct to not store computed values.
Computation logic can change, and working with a stored computed value to reverse engineer it can be a nightmare - if it is possible at all in some cases.

It sounds like you want to combine the two queries:

SELECT c.id, 
       c.name, 
       c.uses, 
       c.code,
       x.num_used
  FROM CODES c
  JOIN (SELECT ed.code,
               COUNT(*) 'num_used'
          FROM ENTRY_DATA ed
      GROUP BY ed.code) x ON x.code = c.code