Request For Help With Data Structure - Are Four-Way Composite/Junction Tables Feasible?

[orange]

To test the evolving model we need test data and some sample scenarios.

[FALAGELOLOGIST]

Hi again,

It looks like I am still struggling with this data structure. Again, I apologize for not giving more information as the actual contents of this database are sensitive so I have to use fake data labels (Circuits, modules, encryptions, codes aren't the actual name of the tables, but they are so similar they might as well be). I was able to come up with a set of rules though. Big thanks for all the support!

Rules

• A single Circuit can have multiple Modules on it (Circuit_Batman can have Module_BlueJays and Module_Expos)
• A single Module can be on multiple Circuits (Circuit_Batman can have Module_BlueJays, Circuit_Superman can have Module_BlueJays)
• A single Circuit can have multiple Encryptions (via multiple Modules with different Encryptions)
• A single Module can have multiple Encryptions, even if on the same Circuit (one module on the same circuit can hold two encryptions within it, each with a different code)
• The same Code can live on different Circuits, as well as twice on the same system (via different Modules)
• The same Code can live on different Modules
• Every instance of a specific Code will always have the same Encryption, regardless of the Circuit/Module it is attached to
• -- And therefore, every unique combination of a Code and Module will always have the same Encryption

As one can see, it is very complex. I have made lots of databases in the past but not one of this complexity. We are also missing data, for example we know a Circuit has specific Codes on it but we don't know the Modules and Encryptions causing that Circuit to have them (we will eventually but the stakeholders are very slow in providing us this data) and they still want that to be recorded.

As I said in the beginning, right now I just have five tables. One for Circuits, Modules, Encryptions, and Codes and a fifth table just with four columns (CircuitID, ModuleID, EncryptionID, and CodeID). It is working well enough for me to build a clean and functional database for the time being but I know this isn't correct. There is a better way.

How can I help those trying to help me? Would providing a better sample than the one I provided earlier help? Again, I have to be careful when doing so but I will give it my best shot. Thank you all!!

[orange]

Did you check your rules against the draft model I provided?

I would check it on paper with some mocked up data, then adjust the model as needed.

See this stump the model article for ideas.

Good luck

[John_G]

A single Module can be on multiple Circuits (Circuit_Batman can have Module_BlueJays, Circuit_Superman can have Module_BlueJays)

I'm still confused with that one. Does it mean:

A) that one occurance of a module (type) can be on two or more circuits at the same time, or
B) that separate occurances of Module_Blue_Jays can be on multiple (or the same?) circuits

Here's an analogy. In English we will often say something like "these two families own the same truck - a white F150". This almost always means that there are two distinct trucks (=modules) (different licence, address, etc), which happen to be the same model and colour, a white F150 (=Blue_Jays). But the two trucks are distinct entities, as are the two modules (I think).

So, does your scenario use option A) or B)? It may seem like there is no difference, but how you model them will be quite different.

In your bulleted list, you reference an entity Systems. How do they fit in - are Systems the same thing as circuits?

[FALAGELOLOGIST]

I'm still confused with that one. Does it mean:

A) that one occurance of a module (type) can be on two or more circuits at the same time, or
B) that separate occurances of Module_Blue_Jays can be on multiple (or the same?) circuits

Here's an analogy. In English we will often say something like "these two families own the same truck - a white F150". This almost always means that there are two distinct trucks (=modules) (different licence, address, etc), which happen to be the same model and colour, a white F150 (=Blue_Jays). But the two trucks are distinct entities, as are the two modules (I think).

So, does your scenario use option A) or B)? It may seem like there is no difference, but how you model them will be quite different.

In your bulleted list, you reference an entity Systems. How do they fit in - are Systems the same thing as circuits?

Apologies, yes! System meant circus. Made the edit. And yes it would be like two families both owning the same model/color of pick up truck. But it's possible one family has the 2013 model and another family has a 2014 model.

[orange]

As one can see, it is very complex.

All the more reason to see some sample data. You don't have to give away any secrets, make up data if you have to, but give us sample data for all entities. We're trying to help you, but we shouldn't have to guess what the issue is, what data values are applicable.
You can test your model with some sample data --either it works or it doesn't. If it doesn't , then you'll have to determine where/what is in error.

[John_G]

A single Module can have multiple Encryptions, even if on the same Circuit (one module on the same circuit can hold two encryptions within it, each with a different code)

Sorry to be so picky - but this needs clarification too.

If a module has multiple encryptions on it, are those encryptions associated with the codes that are on the module, or are they independant? In other words, can a module with no codes on it yet have any encryptions on it? Another of phrasing the question is: Are the encryptions "on a module" associated with the module itself, or are they just associated with the codes that happen to be on the module?

This is a really good exercise for you (and everyone following this thread), showing you how precisely the requirements for a database have to be laid out, and the questions developers have to ask. This is arguably the most important step in developing a well functioning, reliable database.