2

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?

MDCCL
  • 8,520
  • 3
  • 30
  • 61
Intern87
  • 121
  • 5
  • 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
  • @JonathanFite I take you point about removing the TypeXId fields, it makes perfect sense. The problem I have with @George.Palacios comment is that I would have a lot of NULL values throughout this table where 2/3 fields do not apply to the type represented in the row. Additionally, there are more tables that come off each TypeX table unique to each type. – Intern87 May 23 '17 at 13:11
  • 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
  • If you use the solution of adding multiple foreign keys, you should add a CHECK constraint that requires exactly (or at most) one of them to be populated. – cliffordheath May 24 '17 at 05:48
  • @cliffordheath yes good point. Also I'd like to add I've since had a look around and this scenario is covered in 4th normal form. – Intern87 May 24 '17 at 06:08

0 Answers0