Dealing with a lot of variables



  • How to keep things organized when a lot of variables come into play?

    For instance, when you need an search engine. You need a lot of variabeles to narrow the results down.

    So many variabeles come into play, it gets difficult to see the woods through the trees.

    Especially when variable names like these are used:

                Get psMakeId_1          to gsMakeId_1
                Get psMakeId_2          to gsMakeId_2
                Get psMakeId_3          to gsMakeId_3
                Get psConstructionId_1  to gsConstructionId_1
                Get psConstructionId_2  to gsConstructionId_2
                Get psConstructionId_3  to gsConstructionId_3
                Get psConstructionId_4  to gsConstructionId_4
                Get psConstructionId_5  to gsConstructionId_5
                Get psConstructionId_6  to gsConstructionId_6

    Or is it just me?

    Is there any design method or something to keep things organized? Schematics or something?



  • I would recommend using fixed-size arrays (i.e. int psMakeId[3], gsMakeId[3], psConstructionId[6], gsConstructionId[6] etc.) instead of a multitude of variables and then use memcpy to copy them instead of using multiple assignments.

    BTW, the example you posted looks REALLY alarming.



  • Why do you need so many variables?  Without knowing all the details, it looks like you could simplify thing by using some type of collection. 

    As far as a search engine goes, I've never used so many.  Now most of my recent endeavors have been strongly typed searches where I was searching on a few key attributes of the entities via drop downs - a couple free text but not many.  They've been simple "AND" searches - I don't expect shop floor workers to fully understand boolean algebra so no advanced and/or branches. I store the parameters in a separate SearchOptions class which then gets passed into the Dal.  On the database side, I generate parameterized dynamic sql and execute it.

    Perhaps you could explain a little more the specifics.

     



  • I did not write that, I have been staring at it all morning, thinking: "WTF".

    I wrote my own search function, but the external developer said I should use the old one.

    The table structure of the table that has to be searched:

    <FONT size=2>

    NUM FIELD NAME TYPE SIZE OFFST IX RELATES TO FILE.FIELD

    --- --------------- ---- ----- ----- -- ---------------------------------

    1 VEHICLE_ID NUM 12.0 1 1

    2 VEHICLE_TYPE_ID ASC 4 14 7 VHCLTYPE.FIELD_1 (4,1)

    3 DEALER_ID ASC 15 18 2 DEALER.FIELD_1 (3,1)

    4 DLR_VEHICLE_ID ASC 50 33 3

    5 MAKE_ID ASC 15 83 4

    6 TYPE ASC 30 98 9

    7 YEAR ASC 4 128 6

    8 PRICE ASC 50 132

    9 CONSTRUCTION_ID ASC 15 182 5

    10 SERIAL_NR ASC 50 197

    11 PICTURE_COUNT NUM 2.0 247

    12 PICTURE_ID NUM 2.0 253

    13 PUBLISH ASC 1 259

    14 DESCRIPTION TEX 12480 260

    15 DATE_CREATED DAT 10 16643

    16 TIME_CREATED NUM 2.2 16667

    17 DATE_UPDATED DAT 10 16673

    18 TIME_UPDATED NUM 2.2 16697

    19 ResponseCounter NUM 8.0 16703

    20 EXPORT_YN ASC 1 16714 10

    21 EXTRA_ABS ASC 1 16715 7

    22 EXTRA_EBS ASC 1 16716 7

    23 EXTRA_KLIMA ASC 1 16717 7

    24 EXTRA_DAMAGE ASC 1 16718 7

    25 EXTRA_INTARDER ASC 1 16719 7

    26 EXTRA_RETARDER ASC 1 16720 7

    27 EXTRA_HEATER ASC 1 16721 7

    28 EXTRA_EURO NUM 2.0 16722 7

    29 Extra_Allwheeld ASC 1 16728

    30 Extra_Alarm ASC 1 16729

    31 Extra_Asr ASC 1 16730

    32 Extra_Axes NUM 2.0 16731

    33 Extra_Automatic ASC 1 16737

    34 Extra_Fuel NUM 2.0 16738

    35 Extra_Cabine ASC 1 16744

    36 Extra_Cntrllubr ASC 1 16745

    37 Extra_Cruisecon ASC 1 16746

    38 Extra_Used ASC 1 16747

    39 Extra_New ASC 1 16748

    40 Extra_Hygriparm ASC 1 16749

    41 Extra_Hyhammer ASC 1 16750

    42 Extra_Catalys ASC 1 16751

    43 Extra_Taillift ASC 1 16752

    44 Extra_Leather ASC 1 16753

    45 Extra_Airsusp ASC 1 16754

    46 Extra_Navigation ASC 1 16755

    47 Extra_Parkhelp ASC 1 16756

    48 Extra_Discbrake ASC 1 16757

    49 Extra_Quickchng ASC 1 16758

    50 Extra_Steeraxle ASC 1 16759

    51 Extra_Coupling ASC 1 16760

    52 Extra_Wc ASC 1 16761

    </FONT>

     

    I don't know if this is proper normalization, the EXTRA_ collumn's worry me. Does every EXTRA need an collumn. No, right?



  • :O

    Looks like somebody took a shot at designing a database table before learning about relations...

    No, it's definitely not the way one would intend to do it. If you need more than 15 columns in one table, you should revise your design and see whether they are really needed.

    I think you should pull out data off this table and restructure it reasonably in one function / object that would interface with this WTFiness, and make other functions use a more reasonable data format. It's best to keep the stupidity sealed in a can.



  • If there's a more or less fixed set of extras, than this design would work. Saves you from problems with JOINs and you don't have to worry about data integrity. If there's the remotest chance that the set of extras will change over time (and I'm sure there is), then you'll need a separate table of extras and a join table, since it would be an N to M relation.



  • @Skurry said:

    If there's a more or less fixed set of extras, than this design would work. Saves you from problems with JOINs and you don't have to worry about data integrity. If there's the remotest chance that the set of extras will change over time (and I'm sure there is), then you'll need a separate table of extras and a join table, since it would be an N to M relation.

    Assuming that there's a lot of vehicles in the database and a lot of queries go like that: "Looking for a 2002 Chrysler Voyager with AC, 4WD, ABS, CC and leather but without automatic gear", it might be more reasonable to stay with the current table design. Having a "extras" table and a "vehicle_extras" table is definitely more beautiful, but can dramatically hurt performance.



  • Yeah, we stay with the current table design, because there is already a 10000 records in the table.

    However it has sort off ruined my Full-Text Search idea.

    Guess I'll have to parse the search string for each EXTRA.

    When is it viable to do a complete rewrite and database restructure? Taking into account existing data. This app is 6 years old or something.



  • I did not write that, I have been staring at it all morning, thinking: "WTF".

    I wrote my own search function, but the external developer said I should use the old one.

    The table structure of the table that has to be searched:

    <font size="2">

    NUM FIELD NAME TYPE SIZE OFFST IX RELATES TO FILE.FIELD

    --- --------------- ---- ----- ----- -- ---------------------------------

    1 VEHICLE_ID NUM 12.0 1 1

    . . . snip . . .

    </font><font size="2">

    47 Extra_Parkhelp ASC 1 16756

    48 Extra_Discbrake ASC 1 16757

    49 Extra_Quickchng ASC 1 16758

    50 Extra_Steeraxle ASC 1 16759

    51 Extra_Coupling ASC 1 16760

    52 Extra_Wc ASC 1 16761

    </font>

     

    If I'm interpreting that correctly, it looks like most of the Extra columns are merely Y/N "bit" fields.  It only looks like a few of the fields could be normalized, but I'm not a motorhead so I can't be sure.

    I'd probably build a SearchOptions class like the following

    Class SearchOptions

    {

        int[] MakeIds;  // limit to 3 or whatever

       int[] ConstructionId; // again limit;

       . . . Other collection options . .

        int? minPrice; // or you can use a separate PriceRange dropdown

       int? maxPrice;

       ExtraSearchOptions extraSearchOptions;

    }

    Class ExtraSearchOptions 

    {

        bool? hasLeather;

        . . . Other Flags . . .

    I'd use parameterized dynamic sql to execute the search.  I use null values to indicate the user doesn't care about that specific attribute and build the sql appropriately.  The key is to parameterize as much as you can.  I haven't yet figured out a good way to do the collection type parameters without concatenating sql.  I just dynamically build the IN parameter from the list and pass it to the query through an varchar parameter.  Since my UI limits the user to listboxes, I can be fairly certain the only inputs are integers and sql injection is minimized. 

    If others have suggestions on how to deal with collection type parameters, I'd love to hear it.

     


Log in to reply