I have a categories
table, which have two columns, id
and name
.
I have a types
table, which have three columns, id
, category_id
and points
.
I have a products
table, which have three columns, id
, type_id
and customer_id
.
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).
My solution:
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.
Example:
/* 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).
JOIN
s and aGROUP BY
in it. If you post it, we may be able to help you improve it if it doesn't do what you expect it to.