How to make a dynamic Relationship with versioning in SQL?

Question

I don't really know how to name what I'm trying to do, so I better explain it with a concrete case.

I have tree tables : Product, ProductionReport and CommercialReference.

A CommercialReference have one Product and a Product can have multiple CommercialReference. Also, a ProductionReport have one Product and a Product can be in multiple ProductionReport.

My problem is that I want to do some versioning on my Product. So we can have "Gold Wheat v1, v2, etc...". My CommercialReference should only point out the "current active" version of my Product (with an ActiveVersion bit by example), but the ProductionReport should only point the active Product at the moment of the production.

How can I make relationship between ProductionReport and Product and between CommercialReference and Product with these conditions ?

Thanks a lot in advance for your answers.

Note : My Product table have a double primary key (Id, Version) so I don't have problem to make a relationship with the ProductionReport table but, of course, it's not what I want to do with my CommercialReference table. Basically, what I want to do is : Table's description

Answer 1

You're missing an entity. You need something like Product, and ProductVersion. So that you have a table with PK of just ProductID to use for CommercialReference, whereas ProductionReport references ProductVersion.