I am using SQL Server 2005, and I'm trying to figure out the best approach with the strengths and limitationns of it for storing large amounts of data that need to be accessed quickly during calculations.
A little background, my company tracks skill sets of mechanics. Right now, with about 2000 mechanics in the system, they average about 17 pieces of equipment. Each piece of equipment can have up to 664 different skills that need to be rated. The ratings are from 0-9, but can also be NA (Not Applicable) or NR (Not Rated). The skills are broken up into chapters and sub-chapters. Sub-chapters can only be integers (or NA/NR), but the chapter ratings are averages of the sub-chapters, so they are floats.
Right now, the information is stored as each piece of equipment is a record, and the ratings are stored in a string, such as |5752:6|5681:6.00|5753:6|, with 5752 and 5753 being sub-chapters within 5681. Obviously, this string is typically much longer. This has worked fine for pulling out data for a small group of people (20 - 30) on a small number of pieces of equipment (5 - 10), but now we're wanting to provide trends in the data as opposed to just aggregate data.
One solution that I think would make the calculations easier would be to have a separate ratings table, with each rating being its own record, but that would end up being a ridiculous number of records, especially as more mechanics are put in the system (we're hoping to reach the 10,000 mark by the end of the year).
I don't know all of the capabilities of SQL Server, so I don't know if there's something I'm missing that will either help with sorting through the data that's already there, or will provide a better way to store the data itself. I'd really rather not have to migrate all the data to some other format and have to rewrite the code to set and get the data, but if that's the only solution...
That's not what I wanted to here... I've thought since I started here that having the string was a bad idea, but it seemed to work fine for the most part. The idea of going through all the code (guy before me didn't leave me with clean code at all!) has always made me put it off.
I just checked, and with normalizing it, there'd be about 1,400,000 records for the 2000 people. The table ends up being about 27MB. I set the rating field to a tinyint and only loaded the sub-chapters, so the chapter levels would have to be calculated on the fly... I don't know if that's a good thing or not. Right now, we're recalculating the average every time a user changes a sub-chapter rating and storing the chapter average.
I told my boss about changing the way the ratings are stored, and he didn't like the idea. We've talked about it before, but the last time we talked about it, I told him I thought it was more trouble than what it's worth. So, now he has that stuck in his mind...
We don't really have the time or man-power to devote to doing it all at once, so now I'm trying to think of the best way to do a staged migration for the code. I'm not sure if that will involve Views or Triggers, but those are the only 2 ways I can think of at the moment.
I wrote a training DATAbase base on the same ideas
What I did was
Had a 1-5 rating system and 0 if they wanted to be train in it
The only Problem I can see you wanting to mix 0-9 and text (NR and NA)
Just remeber What the Boss see on the report. is just the OUTPUT of the computer doing something.
You need to found what is best for the Job in question and do it that way
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
With the new normalized table I created, I made a bit field to store NA, and there just won't be a record for NR. Those 2 have always been a pain even in the table-valued functions that are used when transforming the rating string to a table format, so it'll be nice to have them handled in a better way.
I played around with using triggers to have the old and new tables update each other, so I should be able to change all the stored procedures and functions over time. I told my boss that, and he was fine with that.
I haven't had a chance to use triggers since my last job a couple years ago. I forgot how useful they can be!