Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Thanked 0 Times in 0 Posts

    Strategy Question: UDF vs Tables (2003/SP3)

    A design question? In one of my databases (inherited) there are complex calculations for profit, costs, etc. These are used throughout in numerous queries and reports. Any change in the method of calculation or a basic factor requires tracking down every instance and correcting it.
    This drives me crazy and is of course a big waste of time.
    So what would be a better design (by better I mean easier to maintain and with the best performance)?
    1) Place all the basic factors in a table and then create queries using these factors that can be referenced through the db.
    2) Create UDF's to calculate profit etc that are used in queries.
    or a mixture?


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Strategy Question: UDF vs Tables (2003/SP3)

    I would create a small set of "base" queries that perform the calculations, and use these to build other queries.
    Whether the calculations use user-defined functions depends: using only built-in functions usually results in more efficient operation, but if the calculations are complex, using only built-in functions could well be too restrictive, or lead to overly artificial constructions. In such situations, user-defined functions can make the queries more manageable.

    How's that for a wishy-washy reply? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    A Magic Forest in Deepest, Darkest Kent
    Thanked 1 Time in 1 Post

    Re: Strategy Question: UDF vs Tables (2003/SP3)

    In addition to Hans's "wishy washy answer" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    This type of approach requires the PC to be switched off and a pen and scratch pad brought to the desk. I have found in the past that when we take over legacy systems they are normally correct when they were designed and to the level of experience of the person designing then and implementing that time Times move on, business does too, therefore changes have to be made in line with current thinking.

    I am currently integrating a system into my customer relationship manegement system, I can't change the base model, but I can change the upper and lower tier to adapt the application. My approach is to look at commonality and to build on the similarities, go for a modular approach, look for common themes and adapt or join the similarities, your business, your call.

Posting Permissions

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