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.
Thanks. ( and sorry for my english, i am not a native speaker ).
asked Oct 20, 2009 at 03:55 PM in Default
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:
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: