x

The Suggested Shopping List Problem...

Imagine a web-based shop where users define product filters like: "its red", "its in the kitchen" or "has the word 'auto' on the name"; and every criteria has a weight that contributes to the final order of the suggested products.

After every criteria added/removed, the system should recalculate and report the top products to any online participant of that list.

This app is usefull when friends dont know what gift to give, and based on general tips the system try to guess the most suited gifts.


Right now i am saving a copy of the product key into a table ( that cant be #temporary or @variable ) and for each new criteria i am adding a new column, which is filled according to the matches it find. ( Upon removal of the criteria, i just update those column with zeros ).

Criterias can be added by multiple users on several days, and that shopping "suggestion" list stays on the database until its finalized ( or after 90 days of inactivity ).

The product list can grow up to millions of products ( slow insert/update statements ), and we have several Full Text Search enabled ( slow select statements ). Scale this search for up to a hundred concurrent users per list and thousands of lists enabled in paralel.

Unfortunatelly this approach is high cpu/io intensive and requires a LOT of disk space. I have spent nights trying to find ways to implement a better solution. Then i find some material about "Long Transactions" and become curious.


A "Long Transaction" can be defined as a standard transaction that can be "paused" and continued after a long period of time, even if that user disconnected and reconnected. It also enables someone to start a transaction and let someone else to complete it.

This concept is interesting in several aspects, but basically it can transform any web-based application into a client-server one, thus avoiding tricks like cookies, "fat" session variables, lots of temporary tables and several others.

  • Does SQL Server ( or any other database ) implements "Long Transactions" ? If not, what could be done to implement anything similar to this in the most automatic way.

  • What other solutions could you provide to solve The Suggested Shopping List Problem ?

Thanks. ( and sorry for my english, i am not a native speaker ).

more ▼

asked Oct 20, 2009 at 03:55 PM in Default

avatar image

Gustavo
592 4 6 11

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Edit: added weight to suggestion list criteria cross table and provided report example

First, the long transaction concept sounds scary. Transactions are used to isolate a batch of changes by performing all necessary locks, making the changes and then deciding to commit or roll back the entire batch. Keeping the transaction open for a long period of time or allowing a different user to continue a transaction both sound like they would cause much bigger problems than they would solve.

What about this database design:

CREATE TABLE products ( product_id int, name varchar(100), description varchar(max) )

CREATE TABLE criteria ( criteria_id int, criteria_text varchar(100) )

CREATE TABLE suggestion_list ( suggestion_list_id int, list_owner_id int, name varchar(100), description varchar(max) )

CREATE TABLE product_X_criteria ( product_id int, criteria_id int )

CREATE TABLE suggestion_list_X_criteria ( suggestion_list_id int, criteria_id int, weight decimal(4,2) )

The last 2 tables are cross-tables for many-to-many relationships. A criteria administrator would be responsible for putting together the list of valid criteria. A product administrator UI would allow them to add or remove criteria that a product meets. An administrator for a given suggestion list (determined by the list owner id referencing back to a users table) would be able to add or remove criteria for that list.

Here's an example of a report:

SELECT TOP(10) p.name AS [Product Name], SUM(slxc.weight) AS [Weighted Score]
FROM products p
    INNER JOIN product_X_criteria pxc
    ON pxc.product_id = p.product_id
    INNER JOIN suggestion_list_X_criteria slxc
    ON slxc.criteria_id = pxc.criteria_id
    INNER JOIN suggestion_list sl
    ON sl.suggestion_list_id = slxc.suggestion_list_id
GROUP BY p.name
ORDER BY SUM(slxc.weight) DESC
more ▼

answered Oct 20, 2009 at 04:33 PM

avatar image

Tom Staab ♦
14.5k 7 14 19

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1069
x414
x291
x73

asked: Oct 20, 2009 at 03:55 PM

Seen: 2069 times

Last Updated: Oct 20, 2009 at 03:55 PM

Copyright 2016 Redgate Software. Privacy Policy