Need advice on how to set this up
-
Here's my issue...
I need to assign a code based on a combination of ProductID and 4 other flags. The 4 flags are all completely independent of each other, and each combination of ProductID and flag (or flags) will have a different code.
Should I create a table with the codes for all possible combinations or should I use a whole bunch of 'if' statements in a stored proc to determine the code to be assigned?
Any help would be appreciated.
-
I think going with the ifs might be the best solution as I don't see any advantages of keeping all possible product codes in the db. Maybe you could hide the logic into a function so that you can reuse that code everywhere in the application.
-
Either approach ought to work. The "best" one is the one that gives you the best trade-off between performance and easy-to-read code. IF or CASE logic is fine if it doesn't get too snarly. A look-up table is fine if it isn't too hard to populate. You also have the option of doing the user-defined function thing. Sorry if this is too general to be of much help, but really I don't think there are any hard and fast rules on this sort of thing.
-
Here's an example for 1 product (1 have 12, total)
ProdID Flag1 Flag2 Flag3 Flag4 Code 1 1 0 0 0 A 1 1 1 0 0 B 1 1 0 1 0 C 1 1 0 0 1 D 1 1 0 1 1 E 1 1 1 1 0 F 1 1 1 1 1 G 1 1 1 0 1 H 1 0 1 0 0 I 1 0 1 1 0 J 1 0 1 1 1 K 1 0 1 0 1 L 1 0 0 1 0 M 1 0 0 1 1 N 1 0 0 0 1 O Wouldn't the IF logic be hairy and hard to follow with that many combinations? I don't know the differences in overhead and performance between selecting from a table and having a stored proc run through a large number of conditions.
-
Go with the lookup table. It's gonna be the simplest way here.
-
Will do. Thanks!
-
Also might I suggest that you move from A, B, C... codes to 1,2,3 >> which would be the equivalent bitmask value of the flags? That way you don't get screwed with missing letters when you need 1 more flag. Also it allows you to index a numeric value that is a good candidate for indexes.
-
Thanks for the tip. The A,B,C was just an example so the missing letter thing won't be an issue, but unfortunately the real Codes are alpha-numeric and I can't change that. These values are getting uploaded to a host system and I have to send them what they need.
-
Ok. Good luck with that project.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply