«

I am an experienced but self-taught ASP programmer, and sometimes wonder
about major issues of principle in programming. For example:

I have a web application that iterates over a set of database records, and
displays each one on screen. The details of how each record is displayed can
vary a lot, so each record might have one to a half-dozen parameters
indicating how it displays, and the total list of parameters that any record
could have tops 40. Which of the following represents better programming
practice:

1) Create a parameters table in the database with a 1:many relationship to
the main records, and as each record is processed in the script, do a
database query to pull associated parameters, and store the resulting
recordset as an array in memory. At each decision point in the script, call
a function to check the parameter array and return values if present.
Advantages of this approach: orthodox database design; meaning of all the
parameters and their values is easily documented in the database.
Disadvantage: one to 50 extra database queries per screen, which will affect
performance and scalability.

2) Code the parameters into one or more fields in the main record (probably
one field for integers and one for text strings) separated by some marker.
For example, 2x0x5 for integers or text1&&&text2&&&text3 for text strings.
As each record is processed in the script, use the ASP split funciton to
create arrays of these parameters. At each decision point in the script,
check the appropriate position in the relevant array. Advantage of this
approach: no extra database query for each record. Disadvantage:
unorthodox, un-normalized database design; parameter sequence and meaning of
the values must be documented in the code, which can get messy; and setting
up the parameter strings for each record can be tedious and error-prone.

Overall, it seems to be a trade-off of hygeine vs. performance. Which
approach is better?

"CB" <> wrote in message
news:BCBF5FCC-0D83-48EF-ACA6-...
>I am an experienced but self-taught ASP programmer, and sometimes wonder
> about major issues of principle in programming. For example:
>
> I have a web application that iterates over a set of database records, and
> displays each one on screen. The details of how each record is displayed
> can
> vary a lot, so each record might have one to a half-dozen parameters
> indicating how it displays, and the total list of parameters that any
> record
> could have tops 40. Which of the following represents better programming
> practice:
>
> 1) Create a parameters table in the database with a 1:many relationship to
> the main records, and as each record is processed in the script, do a
> database query to pull associated parameters, and store the resulting
> recordset as an array in memory. At each decision point in the script,
> call
> a function to check the parameter array and return values if present.
> Advantages of this approach: orthodox database design; meaning of all the
> parameters and their values is easily documented in the database.
> Disadvantage: one to 50 extra database queries per screen, which will
> affect
> performance and scalability.
>
> 2) Code the parameters into one or more fields in the main record
> (probably
> one field for integers and one for text strings) separated by some marker.
> For example, 2x0x5 for integers or text1&&&text2&&&text3 for text strings.
> As each record is processed in the script, use the ASP split funciton to
> create arrays of these parameters. At each decision point in the script,
> check the appropriate position in the relevant array. Advantage of this
> approach: no extra database query for each record. Disadvantage:
> unorthodox, un-normalized database design; parameter sequence and meaning
> of
> the values must be documented in the code, which can get messy; and
> setting
> up the parameter strings for each record can be tedious and error-prone.
>
> Overall, it seems to be a trade-off of hygeine vs. performance. Which
> approach is better?

As a general rule, you should make as few calls to the database as possible.

What do you mean by parameters in the case outlined above? Can you give an
example?

--
Mike Brind

Yes, another option is to pull the main records and parameters in a single
query, but I wouldn't just say "done" -- a lot of clean-up would be required
in the script. If the main records have a one:many relation to the
parameters table, then a single query would retrieve individual records more
than once where they had more than one parameter. The scripts that process
the records would get very complex if they couldn't count on having an array
with each main record appearing exactly once. I would need to do something
like:

1) pull the recordset into an array with getRows
2) iterate over the array to count how many unique records are present, and
how many sets of parameters
3) redimension two other arrays to hold consolidated sets of the main
records and the parameters
4) iterate over the original array again and transfer values into the two
consolidated arrays

Yet another option would be to not use a forward-only cursor, and iterate
over the original recordset twice to build the consolidated arrays of main
records and parameters directly from the recordset.

Possibly these approaches are more efficient than doing a lot of extra
queries for the parameters. That is the hard part about programming: ten
ways to do anything, and difficult to know the processing cost of each
approach.

For example, a record might represent a group of questions to be answered
with radio buttons, laid out horizontally across the screen. Parameters
might be:

-- how to display the labels for the radio button choices across the top; at
least three formats are required depending on the number of choices and type
of question
-- whether to also show numerical labels across the top, if the choices
represent a range from low to high or something like that
-- whether to add an n/a column at the far right so that some questions can
be skipped
-- how much horizontal space to give the question text vs. the radio buttons