Table Structure

[Elliot Liffman]

I'm developing a db to track real estate partnership investments and have
run into an issue.

Some background:

An investment in a partnership is called a Subscription. A Subscription can
be owned by either an Individual or an Entity. In the case of an Entity
subscriber, the Entity is comprised of Individuals as members - however, the
Entity must be listed as the official subscriber of record. Individuals who
own Subscriptions may also be members of Entities that own other
Subscriptions. I've created a junction table (tblEntityMemberships) to try
to handle this situation, but I'm not sure it's the best solution.

Here are the issues:

1. tblSubscriptions contains the foreign keys IndividualID (tblIndividuals)
and EntityID (tblEntities), but for 95% of the records only one of these
fields is used. This strikes me as poor table design.

2. It's possible for Entities to be members of other Entities. I'm not sure
how to handle this.

Any help would be greatly appreciated.

 

[Ken Snell]

Don't use an IndividualID in the Subscriptions table. Instead, use just an
EntityID.

Then, an entity can contain just one individual, right? So you then create a
list of individuals in an entity.

Something like this:

Subscriptions table:
SubscriptionID (primary key)
EntityID
(other fields related to subscriptions)

Entity table:
EntityID (primary key)
(other fields related to entities)

Individual table:
IndividualID (primary key)
(other fields related to individuals)

EntityMembers table:
EntityID (composite primary key with IndividualID)
IndividualID (composite primary key with EntityID)

 

[Elliot Liffman]

Thanks.

Don't use an IndividualID in the Subscriptions table. Instead, use just an
EntityID.

Then, an entity can contain just one individual, right? So you then create a
list of individuals in an entity.

Something like this:

Subscriptions table:
SubscriptionID (primary key)
EntityID
(other fields related to subscriptions)

Entity table:
EntityID (primary key)
(other fields related to entities)

Individual table:
IndividualID (primary key)
(other fields related to individuals)

EntityMembers table:
EntityID (composite primary key with IndividualID)
IndividualID (composite primary key with EntityID)

 

[RSGinCA]

That doesn't seem to account for: "It's possible for Entities to be members of
other Entities."

Rick

 

[Elliot Liffman]

Do you think the answers lies in a self-join? If so, how do I accomplish
this?

 

[Ken Snell]

Elliot -

I apologize for not getting back to you sooner. I am tied up today on some
work stuff, but will get a post to you later today re: the "entity is a
member of another entity" issue. I missed that question in your original
post.

 

[Ken Snell]

A slight modification to the table structures should give you the ability to
have an entity be a member of another entity. We'll delete the Individual
table, add an EntityType table (this table is the type of entity: group,
individual, etc.), and add a field to the Entity table:


Subscriptions table:
SubscriptionID (primary key)
EntityID
(other fields related to subscriptions)

Entity table:
EntityID (primary key)
EntityTypeID (foreign key)
EntityName
(other fields related to entities)

EntityType table:
EntityTypeID (primary key)
EntityTypeName (such as Group, Individual, etc.)

EntityMembers table:
EntityID (composite primary key with IndividualEntityID )
IndividualEntityID (composite primary key with EntityID -- foreign key
to Entity table on EntityID field)


Then you would need to add the Entity table more than once to a query that
is designed to return the list of individuals in any particular group so
that the query can properly find the records.

 

[Elliot Liffman]

Thanks for sticking with this, Ken. I'm still unsure of a couple of things:
1. Is EntityTypes necessary in this model or is it simply informational?
2. Am I correct in assuming that, besides when building queries, I'll also
have to add another instance of the Entity table in the relationships window
to create the two relationships from EntityID to the EntityMembers table?
3. In the EntityMembers table, is there a clear benefit to using the dual
primary key as opposed to an auto-number primary key along with a unique
index on EntityID and IndividualEntityID?

From what I've been reading, this sounds like the dreaded many-to-many
self-join. If so, based on the horror stories I've heard, I'm not sure I
have what it takes to pull it off ;-)

 

[Ken Snell]

Answers inline....

--
Ken Snell
<MS ACCESS MVP>

Thanks for sticking with this, Ken. I'm still unsure of a couple of things:
1. Is EntityTypes necessary in this model or is it simply informational?

This table is not mandatory. I suggest it so that you have an easy way to
"tag" a name to an entity type (assuming that you use just a long integer as
the ID) rather than using a "name" as the EntityTypeID itself. It also
provides a simpler "source" of providing a row source for a combo box that
you might use for selecting the entity type on a form, as you would just use
a simple query (no need for DISTINCT, etc. options) to get the list. You can
omit it if you wish.

2. Am I correct in assuming that, besides when building queries, I'll also
have to add another instance of the Entity table in the relationships window
to create the two relationships from EntityID to the EntityMembers table?
Yes.

3. In the EntityMembers table, is there a clear benefit to using the dual
primary key as opposed to an auto-number primary key along with a unique
index on EntityID and IndividualEntityID?

Not necessarily a clear benefit, but using the composite key means you don't
need a separate index on the two fields if they're not the primary key. You
would need an index so that you can enforce the inability of a person
entering the same combination into the table more than once. Using the
composite key means that you'd also have to join this table (in a query) on
two fields, not just one; having just one field for the join can be easier
to handle. It's really a matter of preference.

From what I've been reading, this sounds like the dreaded many-to-many
self-join. If so, based on the horror stories I've heard, I'm not sure I
have what it takes to pull it off ;-)

"Dreaded" self-join? It's not as difficult as you may have heard. Just think
of the second copy of the table as its own table. And treat it that way. The
real "difficulty" usually is in remembering that it has a different name and
that you must use that different name when referencing it. If you use
aliases in the query, it's really easy.

ability

< snipped >

 

[Elliot Liffman]

Thanks a lot for your help, Ken. I'll give it a try. If I run into
problems down the road, should I post back to this thread or create a new
one?

 

[Ken Snell]

I'll monitor this post for another three weeks or so. If it goes beyond that
timeframe, start a new thread.