I need to repeatedly retrieve a group of records from a data warehouse fact table based on multiple where conditions.
WHERE (user_d=1 OR user_id=2) AND (branch_id=1 OR branch_id=2)
WHERE (user_id=1 AND branch_id=1) OR (branch_id=2 AND branch_id=2)
I was thinking of doing something like this:
... WHERE CONCAT(user_id,branch_id) IN ('11','22 )
and then creating a new column, turning the concat into a natural key which will be created on insert:
WHERE new_key IN ('11','22')
This will allow:
SELECT queries to avoid JOINS. I have input of user_id and branch_id, and can generate that in the application.
I can put a unique constraint on the key field, and then can use an UPSERT.
Is generating a natural key like this strictly for easier lookups bad practice?
I want to use an IN clause because I will have a lot of combined values to lookup.
I won't have IDs to build the new_key or look it up, it will be values.
I think I answered my own question. It's bad practice because it's redundant data.
The CONCAT will be a better choice off a JOIN. To optimize, instead of storing values in the database, it's better to add a key-value store in front like redis.