0

I need to repeatedly retrieve a group of records from a data warehouse fact table based on multiple where conditions.

Not

WHERE (user_d=1 OR user_id=2) AND (branch_id=1 OR branch_id=2)

but

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?

CLARIFICATION

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.

3

Just use composite keys. If the table's natural key is (user_id, branch_id), use that.

CREATE TABLE whatever
(
    user_id ....,
    branch_id ...,
    PRIMARY KEY(user_id, branch_id);
);

In joins you can do it like you showed:

SELECT ...
FROM sometable s
INNER JOIN whatever w ON (s.user_id = w.user_id AND s.branch_id = w.branch_id)

or pairwise:

SELECT ...
FROM sometable s
INNER JOIN whatever w ON (s.user_id, s.branch_id) = (w.user_id = w.branch_id)

For use with IN, use lists of anonymous records:

SELECT ...
FROM whatever w
WHERE (w.user_id, w.branch_id) IN ( ('a','1'), ('b', '2'), ... )

or if you have a lot of values, where this starts getting inefficient, join on a temp table. Big IN lists are bad whether they're singular values or composite.

  • Thanks. I want to use an IN clause because I have a lot of keys to look up. Also, I want to have the keys made of values, not IDs. I should have specified. – stampede76 Aug 19 '16 at 2:44
  • Value, ID, whatever. same principle. Don't concatenate like that, use composite keys. – Craig Ringer Aug 19 '16 at 6:05
  • Edited, hope that helps. Really, use composite keys. – Craig Ringer Aug 19 '16 at 6:12
  • IN ( ('a','1'), ('b', '2'), ... ) that really helps, thank you. I used a JOIN, and have fields from multiple tables in the WHERE clause. – stampede76 Aug 19 '16 at 12:22

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.