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 |
|
Diverguy
Starting Member
2 Posts |
 Posted - 2008-10-17 : 15:22:39
|
| New forum member here. Hoping someone can help.Maybe I'm approaching this the wrong way. Advice, guidance, and opinions would be appreciated. I have inherited a database that is only partially normalized at the first normal form. By that I mean the primary table has some columns that use ID numbers for values like RepID and TaskID but other columns have the values spelled out like the full county name and the full territory name. This makes it very confusing for the end users to view output straight from the table (Using Visual Studio 2008, Dataset, TableAdapter, ObjectDataSource, and GridView). They end up with something like this:RecordID RepID TaskID County Territory ActivityDate1 14 3 Orange Central 10/20/20072 6 2 Duval North 5/11/20083 20 7 Davis South 6/2/2008Difficult to tell at a glance which Rep did what Task in any given location. Due to extenuating factors, I can't complete the normalization and it would be a step backwards to convert all of the RepIDs and TaskIDs to actual names. We're stuck with what we have. However, I would like the output to the end user to be more easily readable. Similar to this:RecordID Rep Task County Territory ActivityDate1 Joe Sales Call Orange Central 10/20/20072 Jim Client Dinner Duval North 5/11/20083 Bob Presentation Davis South 6/2/2008That could be done easily by modifying the SELECT statement in the TableAdapter but... and here's the kicker... the output must be editable, meaning the end users must be able to use the GridView to Update, Delete, and Insert, too. The Update would need to be able to reference the Rep name, find the correct RepID from the Rep table, and update the master table with the correct RepID. How would you do this?• The database schema can't be scrapped and replaced with a totally normalized layout (though that would probably be best)• Would you use customized SQL statements in the SELECT, UPDATE, and INSERT sections of the TableAdapter?• Someone mentioned Indexed Views. I'm not really clear on what they are but this person said Indexed Views could be created of the main table, joined with the other tables, to output a version that is user-friendly and any updates or inserts are automatically tracked back to the underlying tables. Does that sound right?• Is there another, more efficient way of accomplishing this?Thanks for any input and guidance you might have. John |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-18 : 03:04:03
|
| i would have used comboboxes to prepopulate the rep names and tasks in grid. then as per currently set value i'll keep that rep and task names as selected in each combo. this will give user the ability to change rep and task names to any value which is available in lists. the lists will be populated with the all valid values from master tables which contian rep and task info.The changed values will be submitted to db and updated to corresponding record while clicking update button on grid. |
 |
|
|
Diverguy
Starting Member
2 Posts |
Posted - 2008-10-19 : 16:37:10
|
quote:
Originally posted by visakh16
i would have used comboboxes to prepopulate the rep names and tasks in grid. then as per currently set value i'll keep that rep and task names as selected in each combo. this will give user the ability to change rep and task names to any value which is available in lists. the lists will be populated with the all valid values from master tables which contian rep and task info.The changed values will be submitted to db and updated to corresponding record while clicking update button on grid.
After several experiments, I'm starting to believe you may be right and this may be the only way to make this work. Thanks for your input.John |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-19 : 23:44:22
|
quote:
Originally posted by Diverguy
quote:
Originally posted by visakh16
i would have used comboboxes to prepopulate the rep names and tasks in grid. then as per currently set value i'll keep that rep and task names as selected in each combo. this will give user the ability to change rep and task names to any value which is available in lists. the lists will be populated with the all valid values from master tables which contian rep and task info.The changed values will be submitted to db and updated to corresponding record while clicking update button on grid.
After several experiments, I'm starting to believe you may be right and this may be the only way to make this work. Thanks for your input.John
welcome  |
|
|
|
|
|
|
|
|
Advice? Formatting and updating output.