Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm working on an e-business website to sell books and also want to display the number of available books in the category list.

The category list contains the genre and the format (pocket, ebook, audiobook etc). In each element in genre and format I would like to display the number of available products from the stock list. Of course, when you go deeper into the hierarchy, you retrieve more specific information of available books at that level.

For instance there are 2000 available books in the "medicine" genre and if I select the "pocket" format then there are 200 available books left. In other words, you have combined "pocket" and "medicine" and you know now that there are 200 available pocket books in total in the "medicine" genre.

My question is:

How do you create that functionality without making lots of effort to ask the database about the amount of available products in each element? Do you need to buy a special application or plugin? Please remember that there are millions of books in the database.

enter image description here

share|improve this question

3 Answers 3

up vote 8 down vote accepted

You pre-calculate the data once in a while (24 hours f.e.) - and cache the result (this can range from a special optimized table to a file cache).

No-one will count the amount of books and notice there are only 1800 instead of 2200, it is for indication only and it doesn't have to accurate all the time.

share|improve this answer
    
"cache the result", do you mean that to add all result of the data in the client or server's cache memory? –  Gamer Aug 25 '11 at 13:01
    
Yes. You add the final data to a server side caching solution (which could be memory storage). –  Wesley van Opdorp Aug 25 '11 at 13:43
    
From your experience, to enable doing it, it requires lots of cache memory if I'm correct? If Yes, how much cache memory is it required based on a rule of thumb? –  Gamer Aug 25 '11 at 14:02
    
It's not alot, you are speaking about a category title, it's link and the amount of products and some extra characters for it's serialisation or other data format. It's size is insignificant. –  Wesley van Opdorp Aug 25 '11 at 14:03

If you want it 100% up-to-the-minute accurate ...

Calculate the current situation periodically and cache it, then go and retrieve changes since that time.

The time period of "periodically" is a careful balance based on the volume of data (lean towards a long period) and the volume of changes (lean towards a short period). Keep good statistics, so that you can shift as the balance changes.

share|improve this answer
    
Is there any instruction of doing this in a course of action? –  Gamer Aug 25 '11 at 13:04

I agree with the approach of pre-calculating the figures proposed by Mr. Wesley van Opdorp. His approach will make your on-line processing unaffected by changes in the categories.

There is also another option you may want to consider.

You can create a statistics table with several columns, each representing a level within the tree you show on the left hand-side.

Every time an operation of create, update or delete takes place on a book, the number in the corresponding column of the statistics table is either increased or decreased (update is mentioned here in case you update a category of a book, in which case 2 updates to the statistics table will take place). You can use the statistics table to show the tree.

This will keep the numbers accurate and will have make it very quickly to display your data.

share|improve this answer
    
I'm a newbie in this field and I wonder if there are any instruction of doing this? –  Gamer Aug 25 '11 at 13:01
    
I will be happy to provide some details in a document. I don't think I can attach the document here, so email me if you like on egkareem@hotmail.com. –  Emmad Kareem Aug 25 '11 at 16:03
    
Right now, I have sent a e-mail message to you with subject "document". My e-mail is a___jt@gmail.com –  Gamer Aug 28 '11 at 20:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.