I would like to build a reporting tool in an Oracle database that calculates a score based on the result of multiple fairly complex queries that work on different tables. All of these queries will give me a list of a specific ID if certain criteria match.
Here's an example:
Imagine that there would be three queries "Find all user IDs that live in Seattle", "Find all user IDs that bought a car during the last three months" and "Find all user IDs that moved within the US more than 3 times". Each of these queries would get a score for a user if the user ID is found for a query e.g. query 1 = 3 points, query 2 = 5 points, query 3 = 2 points. The end result should give a list of users and their IDs that have a match for at least one of the queries. The result set would be ordered by the sum of all scores descending. The result set should be up-to-date.
| user_id | points_lived_in_seattle | points_bought_car | points_moved | sum |
------------------------------------------------------------------------------
| 123 | 3 | 0 | 2 | 5 |
| 456 | 0 | 3 | 0 | 3 |
| 789 | 0 | 0 | 2 | 2 |
What's the best way to achieve a reporting tool like this in Oracle? I was thinking to create a view for it but this would be very hard because there could be 20 or more queries that could be added to this in the near future. Also I am worried about the fact that the joins that combines all queries don't work correctly and cause an incorrect result set.
Another option I was thinking about was to create a materialized view for each of the queries and one view that just creates the scoring based on the other views.
How would you go go about solving this problem? What could be the performance impact on the database overall if one the queries is very slow?