I am familiar with database concepts, but I am still new to data modelling in the ArcGIS world. Based on my learning so far, I thought I would be using domains to achieve the desired result of restricting possible options for an attribute, but am beginning to doubt that now. I would like to put together a pair of related tables. The first will contain all available streets types and second the actual streets constrained by the types listed in the first table using the equivalent of a foreign key constraint. First table's attributes: * Unique Key (ID) as a string * Street Type Name * Street Type Abreviation Example record : ST, "Street", "St." Second table's attributes: * Unique Key (ID) as a string * Street Name * Street Type Unique Key * Street Direction Example record : MN1, Main, ST, "East" As you can see, my intention is that the third attribute of the second table is constrainted by the IDs of the first table (ST in my examples). When I started looking at domains, I was assuming that a domain could be dynamically based on a table and so as new entries are added to the underlying table, the domain's list of available options also grows. Is this not the case? Is there another mechanism I should be looking at to accomplish this? Cheers, Paul

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Dynamic domain based on a table?

PaulRichard

I am familiar with database concepts, but I am still new to data modelling in the ArcGIS world. Based on my learning so far, I thought I would be using domains to achieve the desired result of restricting possible options for an attribute, but am beginning to doubt that now.

I would like to put together a pair of related tables. The first will contain all available streets types and second the actual streets constrained by the types listed in the first table using the equivalent of a foreign key constraint.

First table's attributes:

Unique Key (ID) as a string
Street Type Name
Street Type Abreviation

Example record : ST, "Street", "St."

Second table's attributes:

Unique Key (ID) as a string
Street Name
Street Type Unique Key
Street Direction

Example record : MN1, Main, ST, "East"

As you can see, my intention is that the third attribute of the second table is constrainted by the IDs of the first table (ST in my examples).

When I started looking at domains, I was assuming that a domain could be dynamically based on a table and so as new entries are added to the underlying table, the domain's list of available options also grows. Is this not the case? Is there another mechanism I should be looking at to accomplish this?

Cheers,

Paul

Najděte více příspěvků označených jako

Filtering - Relating Data

Accepted answers

jcarlson

You've hit the nail on the head. A relationship class is precisely what you need for this.

As far as utilizing constraints, you won't get that with a relationship class by default, but an Attribute Rule can do this. Strictly speaking, two tables don't even need to be related to have attribute rules enforcing constraints between them, but having relationship classes can make it a bit easier to pull attributes from the corresponding table.

All comments

MarkGo

Todd, add to this the additional frustration of Portal not picking up domain changes, and this issue becomes REALLY mind numbing. No amount of service stop-starts and republishing has convinced Portal to pick up the new domain values, despite them showing in the REST endpoint.

I am at a loss on how to accomplish the seemingly simple task of limiting input options based on a table of values. Every avenue thus far has presented yet another bug, issue or something at odds with what the documentation says is supposed to happen.

Domain does not update when service is republished - Esri Community

ToddTurner2

Your statement that "The whole purpose of a domain is to actually restrict values of a field to a defined list of codes and values" seems to be a uniquely Esri database design view of things. What PaulRichard has described is a fundamentally common functionality in every other database I've worked with over the last 30 years, even including Excel back to the late 90's.
Yes, we want a domain to restrict values to a list of values; but, that list should not have to be static. It should be able to reference another table of acceptable values dynamically (the origin table), and bring other field values along with it (like the Street Type Name and/or Street Type Abbreviation), so that when selecting a value in the destination table, you get a dropdown list of valid, decipherable (like 'Street' or 'Avenue' instead of ID '2' or '3') values from which to choose.

I cannot convey how much frustration and angst this has brought me over my 13 years working with Esri products. It's such fundamentally basic relational database design functionality, I just can't comprehend how or why Esri refuses to implement it in its core geodatabase technologies. (I say core techs, because the QuickCapture team has implemented this concept at an application level. Praise, hallelujah.)

Furthermore, why aren't there well-known examples of attribute rules, python-based domain upates, and the like specifically aimed to bridge this functionality gap. Maybe they're there, but I've not found them in over a decade of regularly searching in disbelief and denial.

PaulRichard

Thanks Josh! This was the confirmation I needed to get me off domains and looking at the "right" way to do this. I did quite a bit of work over the weekend and have been trying things out for myself. I still have much to do, but I just wanted to circle back and thank you for the guidance.

jcarlson

You've hit the nail on the head. A relationship class is precisely what you need for this.

PaulRichard

Thanks for replying Josh!

Yes, the first table is, in a sense, the domain. However, my train of thought was that as a domain only allows one "attribute" (the description) so a table is required to accomodate additional attributes.

If I were doing this directly in SQL, I would simply setup the second table's third column as a foreign key to the first tables's first column. That would restrict the street type to only what is defined in the first table.

Taking this to the next level, I fully expect that the Key of the second table will itself appear in other tables or feature classes (e.g. in a table of addresses : HouseNumber, Street Unique Key). So to answer your question, yes, I could certainly imagine using the Street table as the source of a dropdown list of valid streets on the web or the desktop.

From your response, I get the sense that I've gone down the wrong path and am too hung up on domains. I haven't experimented with them yet, but is a relationship class the better approach?

Could I implement the second table to have it constrained on the first as described?

More specifically, when adding a new row to the second table, will the foreign key (3rd attribute) be forced to match an existing Key from the first table?

Looks like I've got some reading to do on relationship classes.

Paul

jcarlson

The whole purpose of a domain is to actually restrict values of a field to a defined list of codes and values. You would need to manually edit the domain to add new values to it. From the sounds of things, you're almost planning to use that first table as a domain?

There might be a way to have a dynamically populated dropdown list for filling in that field, but implementing it could be tricky or outright impossible, depending on where you're planning to use this. Do you foresee web editing for this, or are you doing it in a desktop environment?

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.