Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    168

    Unanswered: Denormalized vs Normalized Table

    I used to have this denormalized table :


    ID Score_1 Score_2 Score_3 Score_4 Score_5 Score_6 ... Score_100
    AAA 1 2 3 4 5 6

    and I have a query which has a total of several scores. For example :

    Score_1_3 = Score_1 + Score_2 + Score_3 = 6
    Score_4_6 = Score_4 + Score_5 + Score_6 = 15

    and so on


    How do I normalize this table and how do I write the new query then ? In the denormalized table, it's easy to calculate the total.


    Thanks.

  2. #2
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Hi Milan,

    The normalisation part is easy, the SQL is tricky.

    Normalising you will end up with two (possibly three) tables. The first table is the parent table with a primary unique key of "ID". Put any columns in this table for data attributes that pertain to, and only to, the ID. Perhaps the ID is a person so the additional columns will be things like Name, Sex, and so on.

    Now we have to consider the scores. There is an implied significance by order here that means there are really two data items - a score number and the score itself. This is where you may choose to create a second parent table for score numbers. Perhaps there is additional data that can be added to the score number table; for example each number may refer to a different test such as Reading, Writing, Arithmetic, and so on.

    The child table holds the scores themselves. It has as a minimum three columns: ID being the foreign key of the parent ID table; Score Number that is the foreign key of the second parent table if you have implemented it or simply a score number; the score itself. The combined key of ID & Score Number should be unique.

    OK now for the SQL and this is where things get messy. If you only want one of your summaries at a time things are not too bad and you need to write a different SQL query for each total. However I suspect you want them all at the same time and the only way I can think of doing this is by using nested IIF expressions. I'm not sure how deep you can nest these - some one out there will tell us - and I'm not convinced that there are not more elegant solutions - again some one will tell us. So here is some sample SQL using as near as possible the example you give.

    SELECT ID, IIf([ScoreNum]<=3,"Score_1_3",IIf([ScoreNum]<=6,"Score_4_6","All others") ) AS TotalType, Sum(Score) AS TotalScore
    FROM Table
    GROUP BY ID, IIf([ScoreNum]<=3,"Score_1_3",IIf([ScoreNum]<=6,"Score_4_6","All others") ) ;

    The Table in the above SQL is the child table. Things get really complex when you add joins and deeper IIF nesting. If I think of anything more elegant myself I will let you know.

    Rod

  3. #3
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Hi again.

    Just realised that you can add another column to the Score Number table. The value in this column specifies to which total the Score Number belongs. So in skeleton the table now looks like:

    Score Number : Total Set
    1 : Score_1_3
    2 : Score_1_3
    3 : Score_1_3
    4 : Score_4_6
    ...

    Join this and the Score table, group on ID and Total Set, and sum on Score.

    This does imply that you do not change your total sets too often.

    Rod

    (PS Sorry can't get the columns above to line up)
    Last edited by Rod; 06-10-02 at 09:41.

  4. #4
    Join Date
    Apr 2002
    Posts
    168
    Thanks a lot Rod.

    It seems to me that Normalization is good for data structure and memory, however, it becomes quite complicated even just to calculate simple totals, I actually other calculations which is more complex. For example, if Score_1_3 is between 0 and 10, Score_1_3_Value is 1 and so on. Denormalization really consumes memory however the query is really simple. Is this true ?

  5. #5
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Hi Milan,

    You have probably hit on a basic truth. Normalisation is good for data, data structures, data relationships, data validation, and is efficient in the respect that is minimises space. However as you rightly point out sometimes the extraction and analysis of normalised data can be a 'bitch.'

    I personally appreciate the normalisation process. It helps me understand the data and relationships, and identify any missing information. There is always a denormalisation process before you can achieve a practical working model and, if you start from a fully normalised model, you can identify what features and facilities are being abandoned. For example you may go back to your client and tell him that this solution will only work for a single organization (because you have eliminated all the structures supporting a multi organization) or that he can only have opening balances each month (because you have collapsed and denormalised the month structure into the account table).

    OK, back on topic. Your new requirement is quite simple to achieve. Again it uses the Iif statement. Once you have calculated your totals then use something like the following:

    IIF(Score_1_3<=10,"1",IIF(Score_1_3<=20, "2","More iif expressions"))

    or if you want numeric results

    IIF(Score_1_3<=10,1,IIF(Score_1_3<=20,2,More numeric iif expressions))

    This presupposes that you do not have too many resulting scores. If they are legion then the sheer complexity of the IIF expression would require a different solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •