I would like to store user purchase custom tags on each transaction, example if user bought shoes then tags are
"SPORTS", "NIKE", SHOES, COLOUR_BLACK, SIZE_12,..
These tags are that seller interested in querying back to understand the sales.
My idea is when ever new tag comes in create new code(something like hashcode but sequential) for that tag, and code starts from
"a-z" 26 letters then
"aa, ab, ac...zz" goes on. Now keep all the tags given for in one transaction in the one column called
tag (varchar) by separating with
Let us assume mapping is (at application level)
"SPORTS" = a "TENNIS" = b "CRICKET" = c ... ... "NIKE" = z //Brands company "ADIDAS" = aa "WOODLAND" = ab ... ... SHOES = ay ... ... COLOUR_BLACK = bc COLOUR_RED = bd COLOUR_BLUE = be ... SIZE_12 = cq ...
So storing the above purchase transaction, tag will be like
tag="|a|z|ay|bc|cq|" And now allowing seller to search number of SHOES sold by adding
tag LIKE %|ay|%. Now the problem is i cannot use index (sort key in redshift db) for "LIKE starts with %". So how to solve this issue, since i might have 100 millions of records? dont want full table scan..
any solution to fix this?
I have not followed
bridge table concept (cross-reference table) since I want to perform group by on the results after searching the specified tags. My solution will give only one row when two tags matched in a single transaction, but bridge table will give me two rows? then my sum() will be doubled.
I got suggestion like below
EXISTS (SELECT 1 FROM transaction_tag WHERE tag_id = 'zz' and trans_id = tr.trans_id) in the WHERE clause once for each tag (note: assumes tr is an alias to the transaction table in the surrounding query)
I have not followed this; since i have to perform AND and OR condition on the tags, example ("SPORTS" AND "ADIDAS") ---- "SHOE" AND ("NIKE" OR "ADIDAS")
Update_2: I have not followed bitfield, since dont know redshift has this support also I assuming if my system will be going to have minimum of 3500 tags, and allocating one bit for each; which results in 437 bytes for each transaction, though there will be only max of 5 tags can be given for a transaction. Any optimisation here?
I have thought of adding min (SMALL_INT) and max value (SMALL_INT) along with tags column, and apply index on that.
so something like this
"SPORTS" = a = 1 "TENNIS" = b = 2 "CRICKET" = c = 3 ... ... "NIKE" = z = 26 "ADIDAS" = aa = 27
So my column values are
`tag="|a|z|ay|bc|cq|"` //sorted? `minTag=1` `maxTag=95` //for cq
And query for searching shoe(ay=51) is
maxTag <= 51 AND tag LIKE %|ay|%
And query for searching shoe(ay=51) AND SIZE_12 (cq=95) is
minTag >= 51 AND maxTag <= 95 AND tag LIKE %|ay|%|cq|%
Will this give any benefit? Kindly suggest any alternatives.