I have a
categories table, which have two columns,
I have a
types table, which have three columns,
I have a
products table, which have three columns,
This means each product has a type, and each type has a category. One category has many types, one type has many products.
I am using MySQL and PHP.
From PHP, given a
customer_id, I want to know how much points that customer has in each category separately (and display a table to the user, for example).
I would first do a query to find what are all category ids, then for each
category_id I would find all types inside that category (that is, all types with the given
category_id); then for each
type_id I would count how many products have both that
type_id and the given
customer_id, and I would multiply that amount for the points of the given
type_id. Adding all the results for each
type_id, I have in the end the total points the given customer has on that
category_id. And as I said, I would have to do this for each
category_id. My solution will work, but seems to me that it would be VERY slow. I am looking for advice on how to solve this problem in a better way - with less queries, or faster queries, or both. The way it is now, it can potentially do as many queries as the amount of categories.
/* TABLE: categories */ name id A 1 B 2 C 3 /* TABLE: types */ points id category_id 1000 1 1 500 2 1 200 3 3 50 4 2 20 5 3 1 6 3 /* TABLE: products */ id type_id customer_id 1 2 13 2 4 13 3 5 13 4 6 13 5 5 13 6 3 13 7 4 13 8 5 13 9 6 13
If the given customer_id is 13, I want get something like the following:
500 total points in category A 100 total points in category B 262 total points in category C
1. What is a good way to do this without changing how my database is structured?
2. Is there a good way to change my database design to allow a better way to do this? (of course I have other fields in the tables that I omitted for simplicity).