[thelist] Database Design Question
Burhan Khalid
thelist at meidomus.com
Mon Feb 28 05:00:47 CST 2005
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
More information about the thelist
mailing list