[thelist] Database Design Question

[Burhan Khalid]

Hello Everyone:

   Have run into a bit of a snag while laying out tables on paper for a 
new website.

   The site runs like this.  Users purchase space (like 20MB).  They can 
then add files to this space.  Once they download a file, the filesize 
in reduced from their available space.

   I'm wondering if its better to store this remaining balance value in 
the database, or calculate it on the fly whenever it needs to be 
displayed?  So far, I have the following (other tables trimmed):

dl_trans
--------
id
ts  (timestamp)
uid (userid)

dl_trans_files
--------------
id
dltid (from the dl_trans table)
fid   (file id -- from a files table)

user_dl_trans
-------------
id
uid (user id)
dltid (dltid -- from the dl_trans table)

   I have this user_dl_trans table for confirmed transactions.  So a 
user may decide to "save my cart" in the program, and that information 
stays in the dl_trans table.  Once they have purchased a product, an 
entry is added in the user_dl_trans table.  I first wanted to add a 
toggle field in the dl_trans table (something like pending) and then set 
it to 0 if a transaction has been fulfilled.  Something told me that was 
not a good idea, mainly because I don't want the table to get bloated 
with completed transactions.

   Anyway, onto my main problem.  One solution is that I check against 
the transaction history of the user, adding and subtracting from their 
available space, till I reach a number which will tell me what is their 
available balance.  The other idea is to have a tracker field in the 
transactions table, which is updated each time user pruchases a product.

   Any ideas?  I'm trying to avoid having this point as a possible 
bottleneck.  The ideal solution would be to have a field that updates 
itself (I think this might be a stored procedure or trigger -- but these 
are not available in the MySQL version that I have).

Thanks for your thoughts,
Burhan