Single Form Field for multiple entities (1 Viewer)

[cnstarz]

I've run into a little bit of a conundrum. My database tracks Effects, Terrains, and Terrain Lists. An "effect" is defined as a change of state of an object that is located on a particular terrain. "Terrain" is defined as the location of an object. A "Terrain List" is simply a list of multiple terrains.

Example 1: Remove virus.exe from ComputerA. "Remove" (change of state) is the Effect I want on virus.exe (the object) that is installed on ComputerA (the terrain).

Example 2: Remove virus.exe from Computer_List_001. "Remove" (change of state) is the Effect I want on virus.exe (the object) that is installed on the computers listed on Computer_List_001 (the terrain), which could have well over 1000 computers.

My "Effect" form has a combo-box called "cbo_Terrain" where I choose the name of the Terrain entity. This is sufficient for Effects that require only one Terrain Entity (such a single Computer Name or IP address, as in Example 1). This is where I run into my problem: what I do for Effects that require Terrain Lists for multiple Terrain entities (as in Example 2)? Not every Terrain entity is going to be added to a Terrain List, but a Terrain Entity may very well be on multiple different Terrain Lists.

I have 1 table for my Effects:

• "tbl_Effects" (pk: Effect_ID): One record for each Effect; has field "Terrain_Name" that the combo-box on my "Effects" form controls.

And 3 tables for all of my Terrains:

• "tbl_Terrain" (pk: Terrain_ID): One record for each Terrain entity; the combo-box on my "Effects" form is populated based off this table.
  
• "tbl_TerrainLists" (pk: TerrainList_ID): One record for each Terrain List
• "jtb_TerrainLists_Terrain" (pk: TerrainList_Terrain_ID, fk: Terrain_ID, fk: TerrainList_ID): Join-table to join multiple Terrain entities to multiple Terrain Lists

How do I make my Effects form flexible enough to allow the user to specify either a single Terrain entity or a Terrain List, while still maintaining Referential Integrity in the event that a Terrain or TerrainList Name has been renamed later in the future? Database is attached below. Thanks for your help!

 

[MarkK]

This sounds like a "many-to-many relationship", but in short, I would never treat the single terrain case. Always treat the problem as a list, and some lists only have one (or zero) items.

 

[cnstarz]

I thought about just making all terrains part of terrain lists, but my Organization doesn't like that idea.

I think I've figured out a very convoluted work-around. I'll have a textbox for single terrains and a combo-box for terrain lists that control the same "Terrain" field. If the user chooses "Single" terrain, then the text field will be set to visible while combo-box will not be visible. If the user chooses "List" then the combo-box will be set to visible while the text box will not be visible. If a Terrain List Name changes later on down the road, I'll have the TerrainList form run an update query on the Effects table that changes all the values of the old Terrain List Name to the new Terrain List Name. That sort of satisfies the "Cascade Update Related Fields" requirement without actually being able to enforce referential integrity.

If anyone has any other suggestions or solutions, please let me know!