Table Design Help

[eliffman]

I'm developing a db to track investment subscriptions for my company's
various offerings. Subscriptions may be made either (1) by individuals or
(2) by entities, where an entity is simply a group of individuals. It's
important to note that in the second case, the entity (not each individual
member of the entity) is the investor of record. Where it gets complicated
is over many subscriptions, an individual investor may be invested in some
as an individual and in others as a member of an entity.

Here's a diagram that will hopefully make my explanation a little clearer.

Individual A =====================> Offering 1
Individual A =====> Entity A =========> Offering 2
Individual A =====> Entity B =========> Offering 3
Individual A======================> Offering 4

I've been toying around with three tables: Individuals, Entities and
Subscriptions but am getting confused by the relationships. Any suggestions
on a good table structure? Thanks in advance.

 

[Eechhutti R.Rajasekaran]

You can create a table with fields Name of individual, Entity, Offering.
You can generate query depending upon the way you need, by filling in the
criteria under
Entity as null to get the particulars where the individual has subscribed as
individual,
Name of entity to get the particulars where the individual has subscribed as
member of the particular group
Or you can use group by function to get the particulars under different
groups.

 

[Mike Sherrill]

I'm developing a db to track investment subscriptions for my company's
various offerings. Subscriptions may be made either (1) by individuals or
(2) by entities, where an entity is simply a group of individuals. It's
important to note that in the second case, the entity (not each individual
member of the entity) is the investor of record. [snip]
Here's a diagram that will hopefully make my explanation a little clearer.

It doesn't help me very much. I think you're trying to cram too many
different kinds of facts into a single table, but I'm not sure.

Individual A =====================> Offering 1
Individual A =====> Entity A =========> Offering 2
Individual A =====> Entity B =========> Offering 3
Individual A======================> Offering 4

Let's try this instead.

Investor of record [Individual A] subscribes to offering [Offering 1].
Investor of record [Entity A] subscribes to offering [Offering 2].
Investor of record [Entity B] subscribes to offering [Offering 3].
Investor of record [Individual A] subscribes to offering [Offering 4].

Granted that might be incomplete, but is it right so far?

What part does "Individual A" play in the following line?

Individual A =====> Entity A =========> Offering 2

Is "Individual A" just one of many members of "Entity A"?

 

[eliffman]

To answer your question, Investor A subscribes to Entity A which in turn
subscribes to Offering 2. In the diagram, I was attempting (rather poorly
it seems) to show that any given Individual may subscribe to some Offerings
as an Individual and to others indirectly through an Entity.

So, one Offering is comprised of many Individuals and Entities. One Entity
is comprised of many Individuals.

Hope I'm being clearer.

I'm developing a db to track investment subscriptions for my company's
various offerings. Subscriptions may be made either (1) by individuals or
(2) by entities, where an entity is simply a group of individuals. It's
important to note that in the second case, the entity (not each individual
member of the entity) is the investor of record. [snip]
Here's a diagram that will hopefully make my explanation a little

clearer.

It doesn't help me very much. I think you're trying to cram too many
different kinds of facts into a single table, but I'm not sure.

Individual A =====================> Offering 1
Individual A =====> Entity A =========> Offering 2
Individual A =====> Entity B =========> Offering 3
Individual A======================> Offering 4

Let's try this instead.

Investor of record [Individual A] subscribes to offering [Offering 1].
Investor of record [Entity A] subscribes to offering [Offering 2].
Investor of record [Entity B] subscribes to offering [Offering 3].
Investor of record [Individual A] subscribes to offering [Offering 4].

Granted that might be incomplete, but is it right so far?

What part does "Individual A" play in the following line?

Individual A =====> Entity A =========> Offering 2

Is "Individual A" just one of many members of "Entity A"?

 

[Mike Sherrill]

Hope I'm being clearer.

Nope. In the first place, you didn't clearly answer any of the three
questions I asked.

To answer your question, Investor A subscribes to Entity A which in turn
subscribes to Offering 2.

What is "Investor A" called when subscribing to "Entity A"? "Investor
A" isn't the investor of record; what is it then? Is "Investor A" a
member of "Entity A"? An employee of "Entity A"? A subscriber to
"Offering 2" through the good graces of "Entity A"?

Turning that around, what is "Entity A" called in the same situation?

 

[Elliot Liffman]

The investor of record, whether it's an Individual or an Entity, is called a
Subscriber. If the Subscriber is an Entity, then the Entity is comprised of
Members (who are Individuals). Therefore, an Individual may be (1) a
Subscriber of an Offering or (2) a Member of an Entity that is a Subscriber
of an Offering.

An Individual being a Subscriber of one Offering does not preclude that
Individual from being a Member of an Entity that is a Subscriber in another
(or even the same) Offering.

The "Entity-as-Subscriber" case is relatively uncommon. Perhaps 5% of all
cases.

 

[Immanuel Sibero]

Hi Elliot,

I sure would like to hear other comments on this since I will be working on
something similar to this.

You definitely need three tables, for example: tblSubscriber, tblOffering
and a junction table tblSubscription. This relationship tells you which
subscriber subscribes to which offering.

The following is the part that I'm not sure of (just a brainstorm):
In addition to individuals, tblSubscriber would also include entities who
subscribe to an offering. You may have to have a flag in tblSubscriber to
indicate whether a subscriber is an Individual or an Entity. With this flag,
you would be able to filter Entity and Individual from tblSubscriber. A
junction table between Entity and Individual would take care of the
relationship between Entity and Individual (say, tblMembership).

Some questions for you:
- Does an "offering" eventually become an Entity? which can, in turn, become
a subscriber to a yet new offering??
- Would there be transfer of membership between Individual in an Entity?
- Would there be transfer of subscription between Subscriber of an Offering?

Immanuel Sibero

The investor of record, whether it's an Individual or an Entity, is called a
Subscriber. If the Subscriber is an Entity, then the Entity is comprised of
Members (who are Individuals). Therefore, an Individual may be (1) a
Subscriber of an Offering or (2) a Member of an Entity that is a Subscriber
of an Offering.

An Individual being a Subscriber of one Offering does not preclude that
Individual from being a Member of an Entity that is a Subscriber in another
(or even the same) Offering.

The "Entity-as-Subscriber" case is relatively uncommon. Perhaps 5% of all
cases.

 

[Elliot Liffman]

Immanuel, I tried unsuccessfully to email you privately. Please send me an
email to (e-mail address removed).

Hi Elliot,

I sure would like to hear other comments on this since I will be working on
something similar to this.

You definitely need three tables, for example: tblSubscriber, tblOffering
and a junction table tblSubscription. This relationship tells you which
subscriber subscribes to which offering.

The following is the part that I'm not sure of (just a brainstorm):
In addition to individuals, tblSubscriber would also include entities who
subscribe to an offering. You may have to have a flag in tblSubscriber to
indicate whether a subscriber is an Individual or an Entity. With this flag,
you would be able to filter Entity and Individual from tblSubscriber. A
junction table between Entity and Individual would take care of the
relationship between Entity and Individual (say, tblMembership).

Some questions for you:
- Does an "offering" eventually become an Entity? which can, in turn, become
a subscriber to a yet new offering??
- Would there be transfer of membership between Individual in an Entity?
- Would there be transfer of subscription between Subscriber of an Offering?

Immanuel Sibero

The investor of record, whether it's an Individual or an Entity, is

called

a
Subscriber. If the Subscriber is an Entity, then the Entity is

comprised

 

[Mike Sherrill]

The investor of record, whether it's an Individual or an Entity, is called a
Subscriber.
Ok.

If the Subscriber is an Entity, then the Entity is comprised of
Members (who are Individuals).

I would have thought the Entity would consist of members whether the
entity is a subscriber or not. I'm sure that depends on what kind of
entity we're talking about. So which is it? Does the entity consist
of members even if the entity isn't a subscriber?

Therefore, an Individual may be (1) a
Subscriber of an Offering or (2) a Member of an Entity that is a Subscriber
of an Offering.

Now, *that's* clearer.

An Individual being a Subscriber of one Offering does not preclude that
Individual from being a Member of an Entity that is a Subscriber in another
(or even the same) Offering.

The "Entity-as-Subscriber" case is relatively uncommon. Perhaps 5% of all
cases.

So do these statements get us closer? Are they right? Incomplete?

Subscriber [Individual A] subscribes to offering [Offering A].
Subscriber [Entity A] subscribes to offering [Offering A].

Entity [Entity A] includes member [Individual A].
Entity [Entity A] includes member [Individual B].
Entity [Entity B] includes member [Individual A].
Entity [Entity C] includes member [Individual A].
Entity [Entity C] includes member [Individual C].

 

[Elliot]

I would have thought the Entity would consist of members whether the
entity is a subscriber or not. I'm sure that depends on what kind of
entity we're talking about. So which is it? Does the entity consist
of members even if the entity isn't a subscriber?

Yes, any entity would be comprised of members. But since this db is
intended to track subscriptions, an entity that is *not* a subscriber of any
offering would not be recorded in the db.

So do these statements get us closer? Are they right? Incomplete?

Subscriber [Individual A] subscribes to offering [Offering A].
Subscriber [Entity A] subscribes to offering [Offering A].

Entity [Entity A] includes member [Individual A].

Right, but note that it's uncommon for an individual [Individual A] to
subscribe to a given offering [Offering A] both directly *and* as a member
of an entity [Entity A].

Entity [Entity A] includes member [Individual A].
Entity [Entity B] includes member [Individual A].
Entity [Entity C] includes member [Individual A].

The above scenario, where an individual [Individual A] who is already
subscribed directly also subscribes as a member of *three* entities [Entity
A, Entity B and Entity C], is possible, but very unlikely.

Entity [Entity A] includes member [Individual B].
Entity [Entity C] includes member [Individual C].

OK

Here's another possibility:
Subscriber [Entity X] subscribes to offering [Offering A].
Entity [Entity X] includes member [Entity Y].