[AccessD] This seems to get me often

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
>