I see the history as a separate entity, thus a separate table. It is like school years for students–I want to see each school year what grade / homeroom a student was in.
In your case, I guess you have to ask yourself: do I need to see the history of a product? Do I need to see where it was 3 years ago? If so, then make a history table and simply put in the product ID and any data that could change between the years (Obviously Location. I don't think Serial number would change).
I see PRODUCTS, LOCATIONS, MANUFACTURERS, MODELS, TECHNICIANS, SERVICE EVENTS and MOVES (or LOCATION CHANGE TRANSACTIONS) entities. Location history is within MOVES.
Serial number, manufacturer, and model are attribute of a product.
for clarity, they are ALL TO's (table occurrences as on the 'graph')
I think you mean a JOIN table (TO) or other RELATED table (TO).
Yes, for 'historical' information about anything a related (or join) table is preferred.
If you have relationships between two tables, a third (JOIN) table is also preferred, if the two tables are main tables. You may only need the relationship to the historical data (a relationship).
Thanks Jeremy for prompt answer. It is important to see all relevant history of a product until it is out of service and sold. Then it can move out of the current inventory table and reside in a separate "historical" table for future referral.
Agree with all the entities you have mentioned. Not sure if i would need one or two TO's of MOVES?
Hi Beverly, thank you for clarifying the TO's. I had meant to ask if I would need two TO's for the same MOVES table