By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,395 Members | 2,537 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,395 IT Pros & Developers. It's quick & easy.

Question of programming principle

P: n/a
CB
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?
Oct 28 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"CB" <CB@discussions.microsoft.comwrote in message
news:BC**********************************@microsof t.com...
>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
Oct 28 '06 #2

P: n/a
sounds like you need 2 tables, one for the main records and one for the parameters.

one query gets the record and all associated params.
done.
Oct 29 '06 #3

P: n/a
CB
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.
Oct 29 '06 #4

P: n/a
CB
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
Oct 29 '06 #5

P: n/a
your sql could selet a set of distinct records. if each record has its own params then there should be no need for further
breakdown....

my experience has always been that if it seems too complex then I need to back up and rethink my approach.
"CB" <CB@discussions.microsoft.comwrote in message news:08**********************************@microsof t.com...
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.

Oct 29 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.