3

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

Improve this question

3 Answers 3

Reset to default
8

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.

Improve this answer
4
  • "cache the result", do you mean that to add all result of the data in the client or server's cache memory?
    – HelloWorld1
    Aug 25, 2011 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, 2011 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?
    – HelloWorld1
    Aug 25, 2011 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, 2011 at 14:03
1

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.

Improve this answer
1
  • Is there any instruction of doing this in a course of action?
    – HelloWorld1
    Aug 25, 2011 at 13:04
1

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.

Improve this answer
3
  • I'm a newbie in this field and I wonder if there are any instruction of doing this?
    – HelloWorld1
    Aug 25, 2011 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 [email protected].
    – NoChance
    Aug 25, 2011 at 16:03
  • Right now, I have sent a e-mail message to you with subject "document". My e-mail is [email protected]
    – HelloWorld1
    Aug 28, 2011 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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