[AccessD] This seems to get me often

Wow, thanks for the memories, John. It's been a while since I walked this
avenue, but I shall try again. Orgs and People are different entitities.
Granted, an org can possibly donate on behalf of itself, but you do not
want to put them (orgs and people) into one table, IMO. Which complicates
the lookup, I recognize that, but we must plod on. We could put them all
into a single table and have a flag that distinguished orgs from people,
and then have queries that respect said flag and behave accordingly. Some
orgs are principally identified with persons (the Bill Gates foundation for
example) but many are not. Then the question becomes, to whom am I
addressing this communique? To a person associated with Org #123 or to the
Org in general? My take on this is that there is always at least one person
who represents the Org of interest. IOW, there is an Org table which may
have one+ Persons associated with it. That's the safest way to go.

Arthur

On Tue, Dec 23, 2014 at 7:51 PM, jack drawbridge <jackandpat.d at gmail.com>
wrote:

> Tina,
>
> I think the topic is supertype/subtype. (generalization/specialization)
> There is an article here.
> http://sqlmag.com/t-sql/sql-design-supertypes-and-subtypes
>
> There is a lot of talk/articles (a lot related to MSSQL), but not many
> sample
> databases.
> Good luck.
>
> On Tue, Dec 23, 2014 at 7:23 PM, Charlotte Foust <
> charlotte.foust at gmail.com>
> wrote:
>
> > I don't answer to Arthur, but yes he did.  I've done it before myself.
> > Have a table of contacts that contains a FK to a company table.  the
> > donation table would have a foreign key for donor which could be either
> > company or contact and another field in donors to tell you which kind of
> > donation it is.
> >
> > Charlotte
> >
> > On Tue, Dec 23, 2014 at 4:13 PM, John R Bartow <jbartow at winhaven.net>
> > wrote:
> >
> > > Tina,
> > > IIRC it was Arthur F. who posted a unique solution to this issue many
> > years
> > > ago. It had something to with one table holding both people and
> > > organizations and having a FK to a lookup that defined which they were.
> > > There was also a M2M table that allowed individuals in the main table
> to
> > be
> > > linked to organizations in the same table and vice versa.
> > >
> > > I forget all the details but I thought it was brilliant solution to a
> > > common
> > > problem. The issues it would have solved for me were numerous.
> > > Unfortunately
> > > the app I was thinking it would help with was too involved to change
> the
> > BE
> > > structure that much anymore. (I had gone down the Org PK < FK >
> > ClientOrg <
> > > FK > Client PK structure route.)
> > >
> > > ARTHUR! Help me out here :-)
> > >
> > > -----Original Message-----
> > > From: accessd-bounces at databaseadvisors.com
> > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tina Norris
> > > Fields
> > > Sent: Tuesday, December 23, 2014 5:05 PM
> > > To: DatabaseAdvisors-Access
> > > Subject: [AccessD] This seems to get me often
> > >
> > > Hi,
> > >
> > > I do keep having this sort of problem to solve.  An example would be
> > that a
> > > donor could be an individual or an organization.  So, a donation record
> > > might have an individual ID as an FK or it might have an organization
> ID
> > as
> > > an FK.  I'm uncomfortable with setting up a table where one of two FKs
> > will
> > > be blank.  Another example would be that a client of a law firm could
> be
> > an
> > > individual or an organization.  Another example would be that a
> telephone
> > > could be associated with an individual, or a household, or an
> > organization.
> > > What is the logical way to handle these?  What am I not seeing?
> > >
> > > Thanks for your guidance.
> > >
> > > TNF
> > >
> > > --
> > > Tina Norris Fields
> > > tinanfields-at-torchlake-dot-com
> > > 231-322-2787
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Arthur