This discussion is archived
7 Replies Latest reply: Jan 14, 2013 7:01 PM by 983060 RSS

Average Score Function

983060 Newbie
Currently Being Moderated
Hello.

I have two things I need to do but I'm not sure how. These problems come up in my EVALUATION_RESULTS table. The first thing I need to do is create a formula for the primary key EVALUATION_ID. I need to do this so that it is the EMPLOYEE_NUMBER followed by EMPLOYER_NUMBER followed by EVALUATION_ID. For instance, if the employee being evaluated has the number 10000, the employer has the number 1000, and the evaluation ID is 100, the evaluation number should automatically be 100001000100. How can I have this be created automatically after the user completes a form?

The second issue is: I need to create a function in the EVALUATION_RESULTS table that takes an average of four scores that are input in the form. So it will take (SCORE1+SCORE2+SCORE3+SCORE4)/4. How can I implement this so that the user enters the four scores, submits the form, and the AVERAGE_SCORE is added to the table.

Thanks in advance for the help.

Edited by: DanDan Sc on Jan 13, 2013 3:40 PM
  • 1. Re: Average Score Function
    Vite DBA Pro
    Currently Being Moderated
    Hi Dan,

    your two requirements are probably not good ideas from a relational design point of view.
    I have two things I need to do but I'm not sure how. These problems come up in my EVALUATION_RESULTS table. The first thing I need to do is create a formula for the primary key EVALUATION_ID. I need to do this so that it is the EMPLOYEE_NUMBER followed by EMPLOYER_NUMBER followed by EVALUATION_ID. For instance, if the employee being evaluated has the number 10000, the employer has the number 1000, and the evaluation ID is 100, the evaluation number should automatically be 100001000100. How can I have this be created automatically after the user completes a form?
    Doing this is committing the error of overloading. This is where a single attribute contains information for more than one attribute. From a relational design point of view, the first thing you should be identifying from your list of attributes is the natural keys. In your case it may simply be the original evaluation id (100). If this is not unique, for example more than one employee or employer may share an evaluation id, then the natural key may be a combination of some or all of the attributes you have noted. In this case the natural key would be a combination of those attributes in what is called a composite key. From an implementation point of view, it may be quite acceptable to then create your Primary Key as a combination of those columns in a composite PK. Another implementation issue is that composite PK's can become unweildy to deal with, especially if they have to be referred to in different entities as Foreign Keys. In this case, you can change the composite PK to a Unique Key and create a single part PK which will be a number that is generated from a database object called a sequence. This is generally called an artificial key. If for some business reason you need to refer your original PK in the format you suggested, as in reports or business receipts, then is is a simple exercise to retrieve it from the database as you require it.
    The second issue is: I need to create a function in the EVALUATION_RESULTS table that takes an average of four scores that are input in the form. So it will take (SCORE1+SCORE2+SCORE3+SCORE4)/4. How can I implement this so that the user enters the four scores, submits the form, and the AVERAGE_SCORE is added to the table.
    Another principle of relational design is that you don't store anything in the database that can be derived from the existing data. Things like your average score should be calculated on the fly when ever your data is presented to the user in forms or reports.

    Regards
    Andre
  • 2. Re: Average Score Function
    983060 Newbie
    Currently Being Moderated
    Ok I will probably just have a unique evaluation number that is unrelated to employee number or employer number. But for the average score, it is a requirement not of my choosing. So I need to know how to implement it in some way. It doesn't have to be how I described, but it is extremely important.
    Thanks in advance for advice on how to do this.
  • 3. Re: Average Score Function
    Vite DBA Pro
    Currently Being Moderated
    Hi Dan,

    Isn't end user driven database design a blast ;-)

    If you insist on doing things the hard way, here are several methods you can use to do this, but they fall into two categories, at the application level ie Apex and at the database level. Usually you will have to do both in order to ensure data integrity as much as possible.

    At the application (Apex) level, you can create a page level calculation which is triggered on submit to populate/update the average field, or you could do a dynamic action that updated the average field each time any of the score fields are updated.

    At the database level you could create a constraint that checks that the average field equates to the average of the score fields and you could create a trigger that updates the average field for inserts and updates.

    Overall, you will need to need to ensure you cover any situation where data can be inserted or updated to ensure that the average field is calculated.

    Another alternative is to use a virtual column, which is a calculated column identified at the database level but the value of which is not actually stored in the database, but is calculated on the fly by the database. These were introduced in v11.2 (I think). I have seen some posts in Apex forums about them and they apparently don't always behave well in Apex, but they may be worth investigating.

    Hope this helps, even if only to convince you that stored derivable values is a bad idea.

    Regards
    Andre
  • 4. Re: Average Score Function
    983060 Newbie
    Currently Being Moderated
    I understand it is a bad idea but it is not my choice. It is a requirement of someone else. I like the dynamic action that updated the field each time a score was entered, as well as the database constraint. The thing is, I am very new to APEX and databases, so I really need information on HOW to do these things :)

    Thanks
    Dan
  • 5. Re: Average Score Function
    983060 Newbie
    Currently Being Moderated
    I actually managed to get it working! Thanks for the ideas!
  • 6. Re: Average Score Function
    Vite DBA Pro
    Currently Being Moderated
    Well Done!

    Now all you have to do is sort out the responsibility issues regarding the design of applications you are building ;-)

    Regards
    Andre
  • 7. Re: Average Score Function
    983060 Newbie
    Currently Being Moderated
    It's for a competition so I think they made it as convoluted as possible! You've been very helpful Andre!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points