This question already has an answer here:

I need to create a database to store Jobs. Each Job can have 1-* Step Each step is classified as one of either Type A, B or C. Each Type has specific details and other tables that join on them so putting them into the Step table is not an option.

This is depicted in the diagram below;

Database model

In this design there is a conditional join between Step and TypeX tables. I have read around and cannot find best practices for this kind of scenario, this feels very messy to me but i can't find a way around it. What would anyone else do?

marked as duplicate by Community May 23 '17 at 16:04

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • You could add an intermediary table, with 4 columns. Column A being the the StepID, columnB being the TypeA ref, TypeB being typeb etc. If a join isn't needed, set the value to NULL. You'll then need to use COALESCE to retrieve values? It does seem messy though. – George.Palacios May 23 '17 at 11:16
  • @George.Palacios thanks for the reply, I did think this but I couldn't help but think it was just pushing the problem onto another table, I will 100% need to do this join so doing this will not even save time on the load. – Intern87 May 23 '17 at 12:16
  • I would make the TypeX tables have the StepID as their primary key/clustered index and not bother with their own Identity. Assuming it's a one to one relationship. As an aside, to @George.Palacios comment, you could put the TypeA,B,C ref fields in the step table without the need for another table. – Jonathan Fite May 23 '17 at 13:01
  • 1
    @Intern87 Establishing some analogies, you may find this Q & A (about a supertype-subtype structure) of help. – MDCCL May 23 '17 at 14:56
  • 1
    @MDCCL This is exactly (and then some) the type of information i'm looking for. Thank you. – Intern87 May 23 '17 at 16:03