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 |
|
Mopani
Yak Posting Veteran
55 Posts |
 Posted - 2009-06-22 : 16:45:47
|
| I am new to SQL and Visual Web Development and am learning basic data manipulation and the presentation thereof - all related to a specific project.I would like some help regarding how to structure my database and which controls/views/procedures would be most suitable to use. This information will take me a huge step forward in my learning curve as at present I am stuck - because I spend more time thinking about how to move forward, than actually moving forward and learning something.I am using VWD Espress 2008, SQL Server and Windows XP. (my database experience is mediocre+ from previously using MS Access)I have a database containing 1 main tables for a Squad of players (this includes various different skills per record), together with numerous other tables containing values (skill_modifiers) used to make calculations upon this table.The process I have followed thus far is to.. 1. build a View doing basic calculations(ie generating new column) on certain fields from Squad. Logic: If ABS(skill1-skill2)>1 then Penalty=(ABS(skill1-skill2)-1)*0.5 2. Then making secondary View to further manipulate certain fields from Squad (using the newly generated values). Logic: If Talent=4 then skill3=skill3+skill3*0.08 3. and making a third View which further generates calculations to generate a Level of Competence (in more than one area). Logic: Competance=(skill1*skill1_modifier + skill2*skill2_modifier + skill3*skill3_modifier) * (fatigue / fatigue_modifier * form/form_modifier) 4. finally a 4th View which applies a ranking to the Competance fields. Logic: rank() OVER (ORDER BY skill1 DESC) AS skill1_rankAll of this gives me the figures I want, and they are correct. But in my mind the process I have followed is incorrect, inflated and inflexible.I have a page which contains.. 1. dropdown list - basically populated with SELECT Distinct Squad FROM dbo.[Team Selection] (gives me either Youth or Senior) 2. gridview - which displays all the fields.Immediately you should be able to see that this gives me the following problems... 1. I can select to view either Youth or Senior - but I cannot get to view them all at once. 2. If I display Youth, the ranking fields are correct, but there are gaps ... since Rank 1 in Competance, might belong to a record that is in Senior.The reasons for this is because of the inflexibility of the way I have coded my Views. I am guessing that I rather need to have code that does what my Views are doing, either on the page, in stored procedures or else in user-defined functions. Which brings me to the position where I am stuck. Due to my lack of knowledge, I do not know in which direction to proceed and which new process to follow.Thus I want help to decide what route to follow and a basic logic of how I would go about getting there. This will enable me to go back and learn how these processes work whilst implementing this new solution.I hope I have made clear what my present predicament is. |
|
|
|
|
|
|
|
How to structure database for flexible retrieve