If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Register one record for each date or one record from and to date?
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. |
#2
|
|||
|
|||
Register one record for each date or one record from and to date?
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." wrote: 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. |
#3
|
|||
|
|||
Register one record for each date or one record from and to date?
On 24 Sep, 12:50, Rui wrote:
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." wrote: 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.– Skjul sitert tekst – – 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 |
#4
|
|||
|
|||
Register one record for each date or one record from and to date?
On Wed, 24 Sep 2008 05:48:29 -0700 (PDT), "Jan T."
wrote: 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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Register one record for each date or one record from and to date?
On 25 Sep, 08:17, John W. Vinson
wrote: On Wed, 24 Sep 2008 05:48:29 -0700 (PDT), "Jan T." wrote: 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. -- * * * * * * *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 |
#6
|
|||
|
|||
Register one record for each date or one record from and to date?
On Thu, 25 Sep 2008 04:11:29 -0700 (PDT), "Jan T."
wrote: 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 -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Register one record for each date or one record from and to date?
On 25 Sep, 22:37, John W. Vinson
wrote: On Thu, 25 Sep 2008 04:11:29 -0700 (PDT), "Jan T." wrote: 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 -- * * * * * * *John W. Vinson [MVP] That was a very good, useful link. Thanx a lot! Jan |
Thread Tools | |
Display Modes | |
|
|