Table Design Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

eliffman - 01 Feb 2004 20:00 GMT

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.

Reply to this Message

Eechhutti R.Rajasekaran - 02 Feb 2004 04:04 GMT

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.

> I'm developing a db to track investment subscriptions for my company's
> various offerings. Subscriptions may be made either (1) by individuals or
[quoted text clipped - 14 lines]
> Subscriptions but am getting confused by the relationships. Any suggestions
> on a good table structure? Thanks in advance.

Reply to this Message

Mike Sherrill - 02 Feb 2004 23:39 GMT

>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"?

Signature

Mike Sherrill
Information Management Systems

Reply to this Message

eliffman - 03 Feb 2004 00:36 GMT

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
[quoted text clipped - 26 lines]
>
> Is "Individual A" just one of many members of "Entity A"?

Reply to this Message

Mike Sherrill - 05 Feb 2004 13:51 GMT

>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?

Signature

Mike Sherrill
Information Management Systems

Reply to this Message

Elliot Liffman - 06 Feb 2004 14:29 GMT

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.

> >Hope I'm being clearer.
>
[quoted text clipped - 10 lines]
>
> Turning that around, what is "Entity A" called in the same situation?

Reply to this Message

Immanuel Sibero - 06 Feb 2004 16:24 GMT

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
[quoted text clipped - 23 lines]
> >
> > Turning that around, what is "Entity A" called in the same situation?

Reply to this Message

Elliot Liffman - 06 Feb 2004 17:55 GMT

Immanuel, I tried unsuccessfully to email you privately. Please send me an
email to eliffman@hotmail.com.

> Hi Elliot,
>
[quoted text clipped - 53 lines]
> > >
> > > Turning that around, what is "Entity A" called in the same situation?

Reply to this Message

Mike Sherrill - 08 Feb 2004 11:39 GMT

>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].

Signature

Mike Sherrill
Information Management Systems

Reply to this Message

Elliot - 08 Feb 2004 15:26 GMT

> 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].

Reply to this Message