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_6Or 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.