arcgis desktop - Creating spatial "Many to one" join

I'm trying to create what I call a "many to one" join. I don't know if that would be the correct term or not. I have a table that has unique account numbers for mobile homes (i.e. - M1007970) per parcel account number (R0003285). (Many mobile homes per parcel - many to one.) I need to join this table to our parcel geometry - and still only have one polygon per parcel.

So, for example, the table may have three rows that have mobile home account numbers M1007370 on one row, M1007371 on another, and another one with M1059370, but all have the same parcel number R0032585. Our parcel geometry would only have the same field of R0032585.

When joining I have 12,088 mobile home records and 44,103 parcels. If I "keep all records" I have 44,103 records with only 7,947 mobile home account numbers (of the original 12,088). If I join based on "keep only matching records" I end up with just 7,947 records total.

I have done it successfully in the past and created a model. In this model I use the table for the mobile homes to join to the parcel layer (.lyr - the only way you can/could join in a model) based on the parcel account number. I copy the features keeping only matching records to a file geodatabase. From the file geodatabase I then append it into our SDE system. This currently quit working for reasons that I can't fathom, as nothing has changed.

Perhaps someone can convey better than I can what I'm trying to do, and if it's called something other than a many to one relationship (I don't believe it's a one to many...).

Answer

It's sometimes confusing, but it's really a matter of perspective. See this diagram (from this topic) for a reference:

This is an example of five relationships (and three different cardinalities).

1. One-to-many: Parcels are related to the ParcelToOwner table in a one-to-many relationship; one parcel may have many owners (partial ownership).


2. Many-to-one: The ParcelToOwner table is related to Parcels in a many-to-one relationship; many owners own (at least some percentage of) a parcel.


3. Many-to-one: The ParcelToOwner table is related to Owners in a many-to-one relationship; many parcels may be owned (at least partially) by one owner.


4. One-to-many: Owners are related to the ParcelToOwner table in a one-to-many relationship; one owner may own many parcels (again, at least partially)


5. Many-to-many: Parcels are related to Owners in a many-to-many relationship; many parcels may be (at least partially) owned by many owners, and many owners may own (at least partially) many parcels. This is expressed through the ParcelToOwner table and the aforementioned relationships. Most DBMSs cannot express a many-to-many relationship without an intermediary table (source), hence this design.

As you can see, whether a relationship is one-to-many or many-to-one depends on how you look at it.

All that being said, the easiest way to accomplish what you're looking for is to create a query table that creates many identical parcels, one for each mobile home. See this blog post for more details: A quick tip on performing a 1:M join

If all of your data is in an enterprise geodatabase, you could also use a Query Layer to do the same thing, on the fly (no intermediate feature class).