Unanswered

Relating Multiple Entities Etiquette

(0) Share

Report

Posted on by Community Power Pla...

Hi all!

I wanted to know what the proper etiquette is when you are relating multiple entities to each other. Say I have three entities, one for construction sites, one for construction blueprints, and one for buildings at a construction site. I obviously want to relate my blueprints to my sites, and my buildings to my blueprints. I'd do this by having a field in "Blueprints" that looks up to my "Construction Sites" entity, and a field in "Buildings" that looks up to "Blueprints" entity. My question is if I should be making an additional field in "Buildings" that looks up to "Construction Sites" if I want to relate the two, or if a building is already associated to a site because it is associated to a blueprint, and that blueprint is in turn associated to a site.

What's the proper etiquette for this? When you have a hierarchy of 3 or more entities? Is the relationship between those entities intrinsically there because you relate each parent to their immediate child? Or is it each time you go further down the hierarchy you add fields to relate to all the entities above it?

I hope I phrased that well, let me know if there's any confusion.

Thanks!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Community Power Pla... on at

Like (0)

Report

One more thing...

I also wanted to know if the etiquette changes when you have your entities related by many-many instead of many-one relationships. For example, instead of the "Blueprints" entity I have a "Construction Workers" entity. Construction workers have a many-many relationship with "Construction Sites" because they can work at multiple sites. They also have a many-many relationship to "Buildings" because they can work on multiple buildings. In this case, do I need to relate my "Building" entity to the "Construction Sites" entity separately (via a Lookup field or separate many-many relationship,) or are they intrinsically linked because of the "Construction Workers" entity's relationships to my two aforementioned entities? Does anything change if I want my relationship between "Building" and "Construction Sites" to be many-many vs. many-one?

Thanks!

Was this reply helpful? Yes No
notj 782 on at

Like (0)

Report

Well, a lookup is going to create a 1:N relationship. If you want to make a N:N, in my experience, you manually create that relationship.

Then what I will do is add a subgrid on the form for the N:N relationship. So if the main thing I want to track is current projects, or buildings being built, I'll have a separate tab on my Buildings form that has a subgrid on it for related Workers entity. Make sure to select the "Show related records".

For lookups where you're just selecting one, say a Site Lead(who also exists in Workers), you just add the lookup control and it will create that relationship automatically.

I'm definitely no Dynamics Pro so I'd like to hear others thoughts as well. That's how I currently do it though.

Was this reply helpful? Yes No
notj 782 on at

Like (0)

Report

@Anonymous I'm not sure if the forums here still don't notify on replies, so I'll tag you so you see my message above. Let me know how it works out for you.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@notj

Yes, I saw your reply. I think what you're getting at is that I have to create those relationships separately. Just because A is related to B, and B is related to C, doesn't mean that A is related to C. Or maybe it does, but it makes for a more complicated lookup in PowerApps.

Was this reply helpful? Yes No
Verified answer

GarethPrisk 2,828 on at

Like (1)

Report

CDS is a relational database. This means that you can establish relationships between the tables, using 1:N, N:1, or N:N relationships.

When you create a lookup field on Entity B to entity A, you have created a relationship between the two. This single relationship would implies that many B records can lookup to (be related to) a single A records.

If you were to add a third entity, you would be able to relate it to either of the others, but nothing happens automatically. Meaning, adding a lookup from C to B, doesn't directly relate A to C.

That being said, the relationship Behavior is important to consider. By default, if you create your relationship as a lookup field, it will be relational. There are two other types of behaviors: parental and configurable cascading.

This means that you can control the behavior of the relationship between the two entities. A common example is parental, where you want B records to be deleted when parent A is deleted. You can also invert that and restrict deletion - meaning you couldn't delete A if a B exists. This prevents oprhaning of records - i.e. incomplete data.

The N:N is always referential. It just allows you to easily say that any A can be related to any B. That's it though, you can't define how they are related or understand when the relationship was made, or restrict deletion. This is when people resort to a manual N:N (intersect) entity approach. You'd create an entity like AB, and have a lookup (N:1) to each A and B. Then it's an entity that you can add fields to, set behaviors, etc. It does require that you create the records, though, vs. simply relating one to another.

TL;DR version. CDS does require that you create all the relationships that you need. You can control behaviors between relationships, but it won't create any relationships for you. This may seem like a burden, but with some basic planning (create an Entity Relationship Diagram (ERD) in Visio or equivalent), it can quick and easy to implement the data model that meets your requirements.

Was this reply helpful? Yes No