Register one record for each date or one record from and to date?

[Jan T.]

What is best practice? Either to

1) Register one record for each date or
2) Register one record with from and to date?

Let's look at an example. If I register vacation for several persons,
whould it then be enough to
register Personid, From- and ToDate?

Later I will want to use those data in a report that will show how
many employees had vacation
in June, or how many days where my Employees on vacation in, say,
June. I will have a parameter
to select wich period I want to study (i.e. June, July, August and so
on).

I have successfully made a sql that extract the information I want,
but it is rather complex so I
started wondering if I am doing things the wrong way when I have only
one record for each time
an employee is on a vacation.

This is how I have registered it:
Emplid Start End
100 6/1/08 6/10/08
122 6/14/08 6/20/08
100 6/28/08 7/4/08

Or should I rather do it this way?
Emplid VacDate
100 6/1/08
100 6/2/08
100 6/3/08
100 6/4/08
100 6/5/08
100 6/6/08
100 6/7/08
100 6/8/08
100 6/9/08
100 6/10/08

If the latter is the best, I am thinking of making some vba code that
adds all the record
with a loop only requiring the user to fill in from date and to date
and click OK.

SO, what is concider best practice here?

Appriciate very much your help.

 

[Rui]

Theoretically, (have a look at database normalisation) you should not store
information that can be calculated in a database, thus keeping its size to a
minimum.
As you noticed by now, this has some drawbacks. Makes your queries more
complex, and also affects performance.
If space is not an issue, I would definately recomend you to go with the
'Register one record with from and to date' option.

Cheers
Rui

 

[Jan T.]

Theoretically, (have a look at database normalisation) you should not store
information that can be calculated in a database, thus keeping its size to a
minimum.
As you noticed by now, this has some drawbacks. Makes your queries more
complex, and also affects performance.
If space is not an issue, I would definately recomend you to go with the
'Register one record with from and to date' option.

Cheers
Rui














– Vis sitert tekst –

- - - - - - -

Thank you for your answer!
I figured I could do some calculation and keep the database smaller.
i.e. if I want to keep track of 300 employees and each one will have
25 workingdays of
every year then the database will grow with 300 x 25 = 7500 rows. The
other option
is to register pr case of vacation, wich would be, say 300 times 3 =
900 records a
year.

And I agree with you: It makes myqueries more complex, and also
affects performance.
The database is getting very slow in some scenarios.

Jan

 

[John W. Vinson]

Thank you for your answer!
I figured I could do some calculation and keep the database smaller.
i.e. if I want to keep track of 300 employees and each one will have
25 workingdays of
every year then the database will grow with 300 x 25 = 7500 rows. The
other option
is to register pr case of vacation, wich would be, say 300 times 3 =
900 records a
year.

And I agree with you: It makes myqueries more complex, and also
affects performance.
The database is getting very slow in some scenarios.

Jan

When you get to 750,000 rows you may need to start thinking about size
efficiency. 7500 rows is TINY.

If your queries are very slow... fix the queries. Be sure there are
appropriate indexes on the fields that you use for searching or sorting; be
sure you have relationships defined with relational integrity enforced,
between the appropriate tables; be sure your queries are correctly designed
(an art which can take years to learn and even then can lead to firey
arguments).

I would have to disagree with Rui in that I'd suggest storing vacations as a
startdate and enddate, but there are good arguments for both approaches.

 

[Jan T.]

When you get to 750,000 rows you may need to start thinking about size
efficiency. 7500 rows is TINY.

If your queries are very slow... fix the queries. Be sure there are
appropriate indexes on the fields that you use for searching or sorting; be
sure you have relationships defined with relational integrity enforced,
between the appropriate tables; be sure your queries are correctly designed
(an art which can take years to learn and even then can lead to firey
arguments).

I would have to disagree with Rui in that I'd suggest storing vacations as a
startdate and enddate, but there are good arguments for both approaches.
--

             John W. Vinson [MVP]– Skjul sitert tekst –

– Vis sitert tekst –

You are right, I have too check my queries. They are really complex
now
and also consists custom function inside the queries.

My database is very, very slow when it comes to loading forms
espesially.
However, I do a lot of things for the user by vba code, like special
functions or
are responding to events in the form and calculates things on so on...

I sure also like to see the performance after rebuilding the actual
tables and queries.

Thank you very much for your answer!

Jan

 

[John W. Vinson]

You are right, I have too check my queries. They are really complex
now
and also consists custom function inside the queries.

My database is very, very slow when it comes to loading forms
espesially.
However, I do a lot of things for the user by vba code, like special
functions or
are responding to events in the form and calculates things on so on...

I sure also like to see the performance after rebuilding the actual
tables and queries.

Try some of the suggestions at

http://www.granite.ab.ca/access/performancefaq.htm

 

[Jan T.]

Try some of the suggestions at

http://www.granite.ab.ca/access/performancefaq.htm

That was a very good, useful link. Thanx a lot!
Jan