I'm retrieving values from 3 tables in database and trying to make them match. I was wondering how I could manage cache in an elegant way. I can't change the database structure even though one of the tables is absurd.
Currently I'm loading my tables from DB into cache, one cache per parameter, and then try to find existing value in each of the cache one after another.
To give you a better idea, here is an example of my inputs
I have a weird non-1NF table A that hold various non-dependant information :
| Grouping Key | Grouping Name | Value | | Grp_1 | Foo | ABCD | | Grp_2 | Countries | UK | | Grp_3 | Time | 3am | | Grp_A | Text | A | | Grp_B | Text | AAA | | Grp_C | Text | B | | Grp_D | Text | B+ | | Grp_E | Text | B- | | Grp_F | Text | B |
I apoligize for this table, it makes no sense, and I can't change it.
I have a table B that link the grouping keys of the previous table to numeric values. "*" means "any value".
|Item family number | FooType | Country | Time | Text | | 1 | Grp_1 | Grp_2 | Grp_3 | * |
Finally I got a table C of objects with their characteristics
| UniqueId | FooType | Country | Time | Text | | 00001 | ABCD | UK | 3am | A |
What I'm trying to do is, given the table B, find a line a matching line in table C with the help of table A.
What I plan to do is loading a cache per table C column with a tuple of UniqueId and this column, where tables matches.
select a.GroupingKey, c.UniqueId from table_C c join table_A a on a.Value = c.FooType select a.GroupingKey, c.UniqueId from table_C c join table_A a on a.Value = c.Country -- same for Time and Text column in Table C.
Cache would look like this :
-- FooTypeCache | GroupingKey | UniqueId | | Grp_1 | 00001 | -- CountryCache | GroupingKey | UniqueId | | Grp_2 | 00001 |
With theses caches loaded, when I read a line of table B, i'm trying to find a matching value in each cache. It would give me for each column of this line of Table B a list of matching uniqueId. If a uniqueId is present in each of these possible uniqueId list, there is a match between this table B line and table A and C.
I find this whole process too complicated. I feel there should be a much easier solution. Do you have any ideas ?