Calculating unique bids on an product

Question

Products (productId INT PK, bidCount INT)

Bids(bidID INT PK, productId INT, userId INT, isCounted BIT, created DATETIME)

There is a 1:many relationship between a product and bids.

Each biding cycle lasts for 1 day, so I need to track unique bids per day and update the bidCount column. I set isCounted = 1 for every row that I process during the batch process.

So this is a running total of unique bids for a product throughout the day.

I have a sql job that runs every 5 minutes to update the bidCount.

Steps that I think I need for this query:

1. grab all rows that have isCounted = 1
2. query#1 build a list of productID and uniqueBidCounts where isCounted = 1 query#2 build a list of productID and uniqueBidCounts where isCounted = 0 query#3 build a list of productID and uniqueBidCounts with the difference between #1 and #2
3. update the products table by adding the uniqueBidCounts to the bidCount column using query #3
4. set the isCounted = 1 for the list in query#2

Is this the right approach? is this a complicated approach or can it be simplified?

Updated I added the DATETIMe field created, so I will have to filter for the current day, and delete any older bids in another batch job run daily (or in the same process)

Answer 1

Why don't you use trigger? If i've understood your problem right, you can put the trigger on insert to the Bids table. In this trigger you will increment bidCount in the appropriate row of products table. And you also can make a schedule, that will run at midnight, and will set bidCount to 0.

Answer 2

I think isCounted is the wrong direction for you. You will have to query for all the bids in that day and group by userId and run a count on the rows and insert that number into bidCount.

So this is a running total of unique bids for a product throughout the day.

You can't really have a running total of uniques because you have to re-unique them every time you run. So whether or not its already been counted is irrelevant.