Need help with complicate relationship data

Hi all
I have this relationship: A hub; B hub and C hub. A is connected with B so we have A_B_link table and A_B_sat table connected to A_B_link.
But C is depending on A-B combination. So if I must do again link table it must be A_B_C_link and to conect A_B_C_sat to it.

  1. Create Hub Tables:
  • Hub A: Represents the core entity A.
  • Hub B: Represents the core entity B.
  • Hub C: Represents the core entity C.
  1. Create Link Tables:
  • A_B_Link: This table captures the relationship between A and B.
  • A_B_C_Link: This table captures the relationship between A_B and C.
  1. Create Satellite Tables:
  • A_B_Sat: This satellite table captures attributes related to the A_B relationship.
  • A_B_C_Sat: This satellite table captures attributes related to the A_B_C relationship.

But like this I repeat A_B relationship.
So can I use link table Id in other link table? A_B_link ID to use in A_B_C_link table?

Sounds to me like you need two different Links.

A_B and B_C, you then use the transitive property of these to get to C from A via B in your downstream joins. Do not have links on links as you suggest.

As for the satellites, are you certain the attributes only make sense together and are part of the same unit of work?

If it’s simply that they are alongside each other downstream in the Presentation Later, then you join these together to do that later; they shouldn’t be modelled as a single satellite spanning multiple hubs/links.

1 Like

As I wrote "But C is depending on A-B combination. " So there is no point to connect only B with C.
You have for example 70 combinations between A and B. And for every combinations you can have different count of C.

Is C really a distinct business entity if it depends on A and B?

A typical situation is where A and B are e.g. store and product, and C is a sales transaction. This would mean that there is a link C that links the store and the product at a particular time. It might be that there is also a hub C’ which is the sales header (as a transaction may have more than one item on it) which also forms part of the link. (plus link sats, eff sats etc)

Can you give an example of what A B and C really are?

Like @alex.higgs suggests, 2 links appears to be correct.

You’re not repeating relationships, you are depicting two seperate and distinct units of work, why didn’t link A B not have that unit of work unless you explicitly modelled it separately, or maybe C is not a business entity — you used the word “dependent”, is it a dependent-child key?

For example:
A- model car
B- body type
C - wheel
A- shkoda oktavia
B- kombi
C - wheel15
So you can have a lot of combinations from A and B and depend on the combination you can have different wheel 15,17,19…

There is a breakdown on what you model as a hub vs simple attributes to a satellite; i’d suggest watching the first few minutes of this