Maintain Historical Data

Cosmos75

Cosmos75

Registered User.
Local time
Today, 14:59
Joined
Apr 22, 2002
Messages
1,280
I have a database that contains the make-up (constituents) of Paints and their usages. I use the usages to figure out emissions for each constituent for all paints on a daily basis. What if one of the paints manufacturers changes its composition in a paint? How do I handle that? I'd have to change the make-up of a paint to calculate new emissions. How do I maintain previous emissions with the old calculations? Do I just have to add the new composition as a new paint with a new name? (Paint 1 version 2)? Or is this beyond the scope of this forum?
 
Yup

Thats exactly the way to do it. Standard terminology however would be Revision or Rev. 2
 
A bit off the subject.

Keeping track of historical data is something that bothers me, so I came up with a database that (hopefully) has inherent in it these problems.

Well, how would I do that for say keep track of sport personalities? Say keeping track of which teams throughput the years Shannon Sharpe has played for? Would I have to have a seperate table for each year then or if they changed posititions?

I've created a small database to try and see how to handle this, attached is a picture of the relationships

League : NFL, NHL, NBA, MLB, WTA...
Sport: Football, Hockey, Basketball, Basebal, Tennis
Position: QB, RB, WR, PG, C, ......

You get the point, I hope...

How would I keep track of of which Positions/Teams a person plays throughout their career.

p.s., I don't have this database populated, just the table structure.
 

Attachments

  • sportsleague.jpg
    sportsleague.jpg
    11.9 KB · Views: 712
What if a player changed his/her name? I want to change the name to reflect that, but want to keep their original name for the years they were going by that original name? Same goes for changing jersey numbers?
 
One more thing about Paints...

Another thing about the Paints, is that the have Part Numbers which are supposed to to be unique and if I used them as Primary Keys I'd HAVE TO change the constituents and not keep historical data.

Of course, the quick fix is to just create an Autonumber Primary Key.
 
For paint formulae or sports team rosters, use the general concept of "In effectivity/Out effectivity".

So you record:
Shannon Sharpe,
Packers,
In Date = 1/2/1999,
Out Date = 3/2/2001

For the current team, you make Out Date = 12/31/9999

Changing tracks; when you receive a shipment of Green Paint #12, you need to know the date manufactured and the formula in use at that date. You use nested queries to determine the formula with Manufactured Date Between In Date and Out Date. So you have to keep a current table of paint formulas and team rosters.

HTH,
RichM
 
Oh, Well... now I know...

I assume that Shannon Sharpe, Packer, InDate and OutDate are in a junction table? And should Shannon Sharpe ever change his name a new entry in Players would have to be added? So the only choice is adding a new entry shoud such a basic thing a players name ever change?

Oh, well.... at least, now I know......
 
You wrote
<<
I assume that Shannon Sharpe, Packer, InDate and OutDate are in a junction table? And should Shannon Sharpe ever change his name a new entry in Players would have to be added?
>>

Good point. I guess there should be a Player table with some AutoNumber key. Then the PlayerHistory table would be:
Player ID,
Team ID, (guess teams change names too :) )
In Date,
Out Date

And just in case Green #12 becomes "Verdant Joy" you want a Paint table too.

RichM
 
THANKS!

RichMorrison and Jerry Stoner,

Thank you for your help! Was wondering if there was another way to get around that but since there isn't I'll know in the future to keep that in mind when designing my table structure!


:D
 
You must log in or register to reply here.

Similar threads

W
Introduction!
Replies
5
Views
852
Wrin Monger
W
K
SQL Code in Access Report
Replies
6
Views
602
Mark_
M
R
To Merge or Not To Merge... Backends
Replies
4
Views
944
The_Doc_Man
The_Doc_Man
K
Solved Performance Issues
Replies
14
Views
1,866
KACJR
K
Jon
Prompt using a board of advisors
Replies
18
Views
2,302
Jon
Jon

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom