Post reply

Need advice on how to set this up

  • September 14, 2005 at 6:37 am

    #102791

    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.

  • September 14, 2005 at 6:45 am

    #589669

    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.

  • September 14, 2005 at 6:49 am

    #589672

    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.

  • September 14, 2005 at 7:01 am

    #589683

    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.

  • September 14, 2005 at 7:14 am

    #589691

    Go with the lookup table. It's gonna be the simplest way here.

  • September 14, 2005 at 7:17 am

    #589693

    Will do.  Thanks!

  • September 14, 2005 at 7:40 am

    #589704

    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.

  • September 14, 2005 at 7:48 am

    #589710

    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.

  • September 14, 2005 at 7:49 am

    #589712

    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