I need to store different datasets coming from a provider where each dataset has it's own release path.
These datasets can be combined together to get the full picture of the data available from the provider.

I know from the doc of the publisher the combination of versions that are allowed.
My pain point is that I need to keep track of the version for each dataset.

Example of data:

"Main" dataset from publisher "ABC" has version "1.0".
"Ext" dataset from publisher "ABC" has version "release_3"

My schema is as follow:






Based on that, is it a problem that FK "VersionId" in tables "Main" and "Ext" references a different record in table "Version" ?

I'm afraid that any user querying the DB will not expect to have diverging Version (as the FK name is identical in both tables).
Unfortunately that's the reality of the data provided by the publisher.

Is there a better design to accomplish the same result ?

NB: It is possible that in the future, datasets from different providers need to be combined.


  • that is only an internal view, what the user actually sees, is only, what you want them to see. and that the main has another version as the others seems also normal
    – nbk
    Commented Nov 21, 2020 at 23:14

1 Answer 1


Technically your Version.DatasetId gets you back to the Dataset table and then you can determine the Provider by ProviderId. So while the Version table can be a little confusing by VersionId, as long as there's no overlapping VersionIds in Ext and Main then you should be ok. Otherwise you'll need to add the DatesetId to Ext and Main too.

From a design perspective for other developers and users, you can create a view that pre-joins the Ext and Main to Version and provide that view to them instead. You can even add a column called IsMain to the view to be extra explicit if you feel.

  • Interesting but what do you mean by "overlapping VersionId" ? Is it different datasets / providers sharing a common version ?
    – Guest975
    Commented Nov 22, 2020 at 8:39
  • Yea without the full picture of how your tables relate (e.g. their primary and foreign keys) I didn't want to assume the VersionId in the Ext table will never be a duplicate of a VersionId in the Main table. If this isn't possible, then you don't have to worry about overlapping VersionIds.
    – J.D.
    Commented Nov 22, 2020 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.