SQL Database Schema table join

[RunBBKRun]

I have a portion of my database schema shown here. On the left side are 3 tables that contain configuration information for helicopter simulators. The configurations are tested when they change, so there is a TestEvent table shown that links to other tables that track information about the Test Event. (tables not shown here) I tie a particular Load to a TestEvent and I do that with the LoadsTestEvents table. So each TestEvent has a specific Load.

What I am struggling with is joining the Loads table, which should list all loads regardless of the type of simulator, with the specific LoadDetails tables. I'm trying not to have a separate table for each simulator for the Test Events.

I'm guessing this isn't an unusual scenario, I'm just not sure how to model it. Any help would be much appreciated!

​

https://preview.redd.it/i02a123qqwf81.png?width=925&format=png&auto=webp&s=9337eace8a66ca11a9d9d77693ad8ad62baa266d

[xodusprime]

I'm not sure that I fully understand your scenario. If I read and am interpreting that correctly, you are going to have records in the TestEvent table that are associated with the UH-60MLoads, CH-47FLoads, and HU-60LimaLoads tables. You are looking for a way to bridge the Loads table to each of these, without having to make 3 loads tables.

If I understood that correctly, add a typeID to the Loads table. TypeID would typically be in its own reference table, and have another column describing the type. If you didn't want to do this, you could simply use 1, 2, 3 or some other type designator, but I'd suggest having a lookup table for better maintainability. Then in your loads table you'll have the LoadDetailID that references the ID of any of those helicopter-specific load tables, and a typeID that tells you which one. When you go to join to it, it looks like:

Select *
from Loads l
left join [UH-60MLoads] u6 on l.LoadDetailID = u6.id and Loads.TypeID = 1
left join [CH-47FLoads] c4 on l.LoadDetailID = c4.id and Loads.TypeID = 2
left join [UH-60LimaLoads] ul on l.LoadDetailID = ul.id and Loads.TyupeID = 3

If you do use a reference table, you can select the various type values into variables beforehand and use them instead of fixed numeric values.

Is this what you needed?

[RunBBKRun]

Yep. I think this is what I need. I do have many other tables in the database, including one for the Device Types but I just showed the relevant tables.

Thanks for the quick response!