OK, I think I just need some more bubble brains on this one—
I’m working on a bad movie database. Each movie is tagged with a number of keywords from an option set.
My current goal is to generate a personalized list of films based on each user’s preferences.
My approach so far is to give some keywords a numerical handicap (for example, “Hateful” has a -4 and “Oops, Good Film” has a +4). Then there will be a preferences form where a user can go down a list of keywords that don’t have a handicap score (“Aliens,” “Nicolas Cage,” etc.) and rank each one from -3 to 3, so that a film’s total score would be its handicap plus the sum of the user’s rankings for keywords that film is tagged with. Ultimately, any film that ends with a total above 1 would be in the user’s list.
I’m struggling with how to set this up on the database side of things. It seems like the answer is for the User thing to have a numeric field for each keyword, but there are so many keywords already that I’d have to add fields for manually—plus this would mean each time we add a new keyword, I also have to add a new field and a new question on the preferences form. I guess that’s correct?
I guess I’m just thinking out loud here a little bit. Does an easier solution occur to anyone?
Thanks for your time!
Edit—Thinking now the better structure is to have a separate table for User-Keyword-Weight that can be used to filter/lookup/sum the user weight for each film. I’m gonna try that and see how it goes.
Autobinding isn’t an option unless every movie has a field for every User/Keyword combination or every user has a field for every keyword. This doesn’t allow dynamic updating of the database when new users join, or new movies/keywords are added. You can only autobind when the thing already exists in the database.
One is User–Keyword–Weight and creates or updates entries whenever a user changes their preferences for a keyword. So if I’m logged in and change my vote for Nic Cage from Yes to Hell Yes, a workflow looks for that User–Keyword entry if it already exists and changes the weight from 2 to 3 (or creates the entry if it’s not there). This table then can be used to calculate the user’s weight for any given film based on the keywords it’s tagged with.
The other is User–Movie–Score. This does a similar thing but with a calculation done in a repeating group on the recommendation page that uses the Instant Calculator plugin to add the movie’s handicap to the user’s sum for the keywords on that movie and update the Score entry each time the page is loaded.
This solution is a little bit slow and will slow down the more movies, users and keywords are added to the database, so if anyone has suggestion for optimizing this process I’d appreciate it. I guess probably the best way to do it would be with API workflows to update the calculations on page load, but as this is just a goofy app for me and my friends, I’m probably not going to upgrade to an account where I can do that :].
