Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-14 : 10:53:03
|
Jason Woolf writes "Hi - Please can you help settle this -
SQL 7.0, ASP, ADO 2.1
We are writing a generic back end application to produce mailing lists from customer characteristics. The customers enter their characteristics when they purchase subscriptions to a particular website (or protected area) from our company.
The back end application will have to work with many different websites run by different divisions in our company and each website may capture different customer characteristics depending on the nature of the website.
Currently we have hit a stalemate between SQL DBA and ASP Developer in terms of database design over coding design and here we really need your help.
The backend being designed using asp will consist of 8 to 20 multiple select list boxs (depending on the selected website we want to mail against) for which each selection field could have between 2 and 150 values.
When a user has made all of the selections they want to, we need to be able to save the selection somehow so we can either run the query or re-edit the selections.
Heres where the differences occur- Lets just work with three select boxs for now called cmbCountry, cmbSubject, cmbLevel. Lets also say that each selection box has the first 3 values in each selected.
I believe I should store these in a table such as
MailList Field Value
ML1 Country 1,2,3
ML1 Subject 1,2,3
ML1 Level 1,2,3
In this way I could easily rebuild the asp form to re-edit the selection list and if the user swapped the values in cmbCountry for values 4, 5 and 6 then I could easily call a stored procedure to replace the Value against ML1/Country
The dynamic stored procedure I will have to build could fetch each ML1 and build a sql statement including "... Where TBL.Country in (@Country_Value)..."
Our DBA's opinion is that storing multiple values in a single field is poor database design (with which I would normally agree) however he wants me to store the data like this-
MailList Field Value
ML1 Country 1
ML1 Country 2
ML1 Country 3
ML1 Subject 1 .... etc etc
Although size is not an issue (in this case :-) if we had 10 selection fields each with 10 values selected this would equal 100 lines of data - my issue with this is that I would need to execute "SP_SAVE_VALUE" 100 times and surely that is not efficent.
If you think of the scenario where my user edits an existing mailing list I would have to delete each item currently saved and save the whole lot again instead of just replacing one string value for each characteristic.
I hope that makes sense - and I would realy appreciate any help or advice you can give
Yours thankfully
Jason Woolf" |
|
|
|
|
|
|
|
How to store and use values to build up a dynamic SQL statement