1

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 ?

  • 1
    If it seems too complicated, then it is. Try another approach. Start with how you would arrange the data if you could, then create a query to fetch exactly that data in that way. In your program, you simply load the contents of this query into your cache and use it. If you're not sure how you would arrange the data, then this is precisely what you need to figure out first. – Neil Jul 17 '18 at 8:38
1

This is something you should be doing at the database level. SQL is built to take sets of data and turn them into other sets of data. you can join these tables together into a single set that holds all the useful information to you and store that in cache. This allows you to only have to look for your key values in one place then you can get all the relevant values to that key in whatever form is most convenient to you.
You should only be caching things that have value to you on their own, checking multiple cached values to get useful data is a sign you need to remodel your data. You don't have to modify the physical structure of a database to get data in a useful format, you can create queries that let you read data in a useful format, and you can write code to translate that back to the physical layout for inserting/updating if required as well. It's more work to work around a poorly designed database, but its doesn't have to make everything harder if you properly abstract the inconsistencies from the rest of your logic.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.