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

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

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.

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 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.

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.