ECommerce, product and stock how to page those

Question

Hi,

I am building a EC system for a client, this client is selling second hand products. My DB scheme is roughly like this

+-------------+ +-------------+ +-------------+
| Category    | | Product     | | Stock       |
+-------------+ +-------------+ +-------------+
| category_id | | category_id | | stock_id    |
| path        | | product_id  | | product_id  |
+-------------+ +-------------+ +-------------+

a product is inside a category, a stock is an item of one product, the stock also contain specific information for that: stock serial, state (good condition, a bit broken, junk).

Now I need to make a page to show product in stock in each category for exemple something like this:

TV (big category)
   LCD (small category)
     SONY super LCD 22'' <- product 
    Stock 1 
    Stock 2
    Stock 3
     Sony super LCD 24''
     .... etc ...
   PLASMA (category)
     Hitachi bla bla plasma
       Stock 1 
       Stock 2
       Stock 3

Since the user can browse first level of category I need to add some kind of paging system or the page would get too long and the server would be loaded for nothing.

My initial plan was to pull all the products in the category and children category, those product would have stock and do a limit on that query. When I got those product I would do n number of query to get the actual stock rows.

Since a good SQL query is better then long explanation

SELECT * FROM category c JOIN product p ON c.category_id = p.category_id 
WHERE c.deleted = 0
AND p.deleted = 0
AND (c.category_id = 37 or c.path LIKE '/1/37/%')
AND (SELECT count(*) FROM stock s WHERE s.product_id = p.product_id AND s.sell= 1) != 0 
LIMIT 10

After I would do 10 queries like this

SELECT * FROM stock s WHERE s.sell = 1 AND s.product_id = pulledid

so here come my question, is there a better way to do it ? Can you some flows in this approach(don't be indulgent I know that's really a naive approach but I cannot come with something else)

Would it be more intelligent to limit on stock ? knowing that the page would have some dynamic html (maybe ajax) to group some that have same price.

Is it better to show: - one product and all stocks (in most case it won't be more then 5 items) - page by stock which means the Product A could be on page 1 and page 2.

If someone have experience building this kind of system and can give me some feedback I would be grateful.

Answer 1

1. Why are you getting the product IDs and then using them individually to query the stock table? It seems like you could just join the stock table.
2. Using your design you will get more than 10 rows if there is more than one item in stock for a given product ID.
3. It is not clear how you are going to get the next 10 for the following page. You need a way to select starting at the next 10.

The normal way I would structure a problem like this this would be to create a query that lists all the results. You could do this by joining the stock table into your initial query. To make the paging work you devise a column or set of columns in the output that corresponds to the ordering that you want in your output, then add an order by clause for that. This set should be unique. In your case it could be something like product ID, stock ID, and a unique stock identifier like an autoincremented column or a timestamp. You can then limit your first page query to the page size. For the next page, select only those records where the unique values are greater than the last one on the last page. In your case, you would be using multiple columns so you might not be able to query this way. An alternative would be to select 2 pages worth for page 2 but throw away the first page and display the second page.

The idea is that for each page you are selecting the same overall set of records in the same order but displaying a different subset.