Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Question Selecting attributes for an Entity

    Hi,

    My question is about selecting attributes for an Entity, The case is
    as follows
    Entity is STUDENT
    Fee recieved per month is recorded, as well as the total fee recieved
    (including the fine) is stored per semester and per year as well.
    My Question is should I store the three values in database or should
    i simply store the fee recieved per month and display other 2 values
    (fee per semester and per year) by calculating using the front end.

    Thanks in Advance..!

    Jawwad Alam

  2. #2
    Join Date
    Mar 2004
    Posts
    370
    Hi,
    My general rule is: if the frequency of queries which use per semester and per year tuition field is high in other word your clients do this many times (many times is relative for you purpose) then save these columns in table.
    Otherwise use computational data and do not save them.It is just for performance and does not anything with database design.
    -Best wishes

  3. #3
    Join Date
    Dec 2004
    Posts
    1
    Quote Originally Posted by Ach
    Hi,
    My general rule is: if the frequency of queries which use per semester and per year tuition field is high in other word your clients do this many times (many times is relative for you purpose) then save these columns in table.
    Otherwise use computational data and do not save them.It is just for performance and does not anything with database design.
    -Best wishes
    Hi Ach,

    Thanks for your suggestion. I was also considering the same thing.. frequency of queries for the fields. I have got another idea, that is to make a view rather than calculating using front end... and making the backend design more descriptive.. What do you say about it..?

    Waiting for your response
    Jawwad Alam

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Quote Originally Posted by Ach
    Hi,
    My general rule is: if the frequency of queries which use per semester and per year tuition field is high in other word your clients do this many times (many times is relative for you purpose) then save these columns in table.
    Otherwise use computational data and do not save them.It is just for performance and does not anything with database design.
    -Best wishes
    My general rule is: do not introduce the possibility of human error and subsequent integrity issues.

    If you can derive the value from existing information, you would be ill-advised to duplicate it elsewhere. A compiled view will most likely be nominal while still providing the "descriptive" backend you apparantly desire.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2004
    Posts
    370

    views?!

    Hi,
    you are right.You MAY use view but note that a view does not have stored data essentialy so it could not gain performance benefits as computed fields.
    but you may use it for design and security and ....
    A computed data using a trigger for example is not error prone dear Teddy (LOL)
    I suggest you look at something higher than ACCESS
    -Best wishes

  6. #6
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    I agree with Teddy that other alternatives should be explored before deciding to store computed fields.

    There is always a cost with storing computed fields. Every transaction must fire the trigger and that incurs a small cost. The space required to store the field incurs some cost. There is always the possibility that the trigger may become invalid, be dropped, etc. Then, an uninformed developer, believing that everything is fine, will end up displaying wrong results.

    With a view, you pay the cost only when the information is requested. Often, the DBMS can optimize the query so the performance hit is negligible.

    Look at the performance with a view. if that is slower but acceptable, use it. Else, use the pre-computed field.

    It is not a question of Access or Oracle or SQL Server. It is a question of database design principles.

    And we'll thank you, Ach, not to resort to crude personal attacks. Debate the merits of the argument. Explain why you disagree with someone. Don't get personal.

    Ravi.
    Last edited by rajiravi; 01-02-05 at 19:03.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Quote Originally Posted by Ach
    Hi,
    you are right.You MAY use view but note that a view does not have stored data essentialy so it could not gain performance benefits as computed fields.
    but you may use it for design and security and ....
    A computed data using a trigger for example is not error prone dear Teddy (LOL)
    I suggest you look at something higher than ACCESS
    -Best wishes
    I'm running access as a reporting engine to MSSQL2k

    If you're going to resort to personal attacks, please be sure you have a solid understanding of the environment you choose to attack. As I'm sure you already knew, adp's use MSSQL and all of it's caveats exclusively with regard to the backend.

    I also find that calc'ing values tends to be quicker client-side then any stored procedure or "compiled view" if you will.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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