Is there a standard pattern for linking products to manufacturers, when we want to capture the fact that manufacturers are interacting with each other over time (mergers, takeovers, etc)?
In our industry, company A makes product 1, and many people refer to that product with a token like "A-1" when talking about it or trying to look up detailed performance and physical characteristics of that product in our database.
If company B purchases A, we still want to be able to allow people to use a token like "A-1" to look up the performance data of that product, even though the owning company is now B. A new user meanwhile can look at the records and understand that company A made the product and is the former owner, while company B is now the "real" owner.
Is it viable to model this with two manufacturer foreign keys, and a version column that would increment when the manufacturer relationship changes? The version column would be used to allow the older "A owns 1" row to remain in the table for existing records that linked to it at the time A owned 1 before being bought out by B.
Best Answer
Use a join table with a date of validity
you'll probably need several indices depending on what reports you want from the database.