Hi, I am going to be making a database that will keep information on radio collared animals. Animals get new collars and new frequencies periodically. Then the old collars are refurbished and the go out on new animals with the already used frequency. So basically I will have a table animal ID, frequency dateOn, and dateOff. Then people prefer to record their observations of these animals by their frequency since its less numbers to deal with. So this would be another table with frequency and date. I hope this makes sense I will try to make example tables.
Tbl_CollarHistory
AnimalID frequency dateOn DateOff
1 151.100 1-1-2010 12-31-2010
2 151.100 1-1-2011 12-31-2012
1 150.200 1-1-2011 12-31-2012
Tbl_Sightings
frequency date
151.100 2-3-2010
In a different database I did find a solution for this type of problem. I created a query with the frequencies joined. Then I made a column that checked if the date was in between dateOn and DateOff. Then I hid the records that were false.
That solution worked, but it took a couple minutes to join 20k and 2k records in each table. This new database will be smaller 200 in the collars and 5k in the sightings. Each frequency typically used 1-10 times at the max.
My question for you is do you think this approach is a good idea? Is there some better way to do this? Unlike the other database, this one will have several users and I am a little worried that it will run slow and be annoying . Should I just change the way people record their sightings so they record the animal ID (although I don't want to do this)?
Thanks!!