Let's say Microsoft has many CDs and it needs to track (including history) the location of each CD .
So there will probably be a CDs table like :
CD_ID | title | ...
_________________________
... | ... | ...
And another table which represents the "moves" :
CD_ID | dateMoved | DestinationCountryID |
___________________________________________
... | ... | ...
And here is my problem :
If Microsoft wants to know where is cd Y is currently at :
It can scan the moves table and get its last county.
But ( and here is my question) :
Something inside my head tells me that I should add a column to the CDs table to be like this :
CD_ID | title | CurrentCountryID | ...
________________________________________
... | ... | ... | ...
So it would be more descriptive per Cd Item. (and easy to find).
The CurrentCountryID will be updated after each move.
But
This will cause redaundant data !
cuz now we have 2 places which represents its current location.
Am I right ? should I add this column ?
(NB if you gonna ask about quantity : lets say we have millions of cd's)
What you should do really depends on your needs. If you're going to look up the current country often and performance needs to be good, I would put the current country on the CDs table. Otherwise you will have to query the moves table every time and get the max date - not very good for performance.
If, on the other hand, you rarely need that information, I would normalise it and leave it only in moves table.