How to design a Sponsor Table, when a sponsor can be of many types

MS Access 2K, Windows XP
====================
Hi,

I have a database with following tables (Primary Key indicated by *) -
1. tblEvent (EventID*, EventName)
2. tblOrganization (OrgID*, OrgName)
3. tblCoalition (CoalitionID*, CoalitionName)
4. tblProgram (ProgramID *, ProgramName)
5. tblCommittee (CommitteeID *, CommitteeName)

An Event can be sponsored by one or more of an Organization, Coalition,
Program or Committee; or any combination (e.g. 2 Organizations and 1 Program
can sponsor an Event together). A sponsoring entity does not exist in more
than one table, for example, an Organization cannot be a Program. So, a Union
query of tables 2, 3, 4 and 5 will be a unique list of names.

I'm not sure how to approach the design of tblEventSponsor to store the
sponsoring entities for the Event.

Approach 1:
=========
tblEventSponsor with the following fields:
1. UniqueID (*) (Autonumber) [I know this is not required, as the other 3
IDs together can work as a Primary Key, but I still like to have a unique ID.]
2. EventID (Foreign Key, from tblEvent)
3. EventSponsorID (Foreign Key, from tables 2, 3, 4 or 5)
4. EventSponsorTypeID

When I design the form, I can have a single sub-form in the Event form to
pick the names of sponsors using a Union query on tables for Organization,
Coalition, Committee and Program to include the respective ID and Name, plus
including an ID for the Sponsor Type (1 = Organization, 2 = Coalition, 3 =
Committee, 4 = Program).
The Union Query will have 3 fields: EventSponsorID, EventSponsorName and
EventSponsorTypeID. I'll hard-code the EventSponsorTypeID in the query.

Approach 2
=========
tblEventSponsorOrganization
1. EventID
2. OrgID

tblEventSponsorCoalition
1. EventID
2. CoalitionID

tblEventSponsorProgram
1. EventID
2. ProgramID

tblEventSponsorCommittee
1. EventID
2. CommitteeID

This would involve creating 4 sub-forms (or 4 combo-boxes), one for each
sponsoring entity.
=============================
I think Approach 1 is better (efficient, less work) than Approach 2, but I
wanted to get some feedback, in case I'm missing something. Are there
advantages to the second approach that I'm not seeing?

Thanks for any advice.

-Amit

Hi Amit,

Another approach would be to introduce an entity "Sponsor" with
subclasses Organization, Coalition, Program, and Committee.

You'd then have a simple M:M relationship between Events and Sponsors,
implemented via tblEventsSponsors (EventID*, SponsorID*), and a set of
1:1 relationships between Sponsors and its subclasses (using SponsorID
as the primary key in tblOrganization, tblCoalition, etc.).

All the fields common to all the subclasses would be in the main table
(i.e. tblSponsors), so EventName, OrgName, etc. would be replaced by
tblSponsors.SponsorName, and so on. Elsewhere in the database, any time
you needed to access fields specific to a particular subclass you'd join
tblSponsors and the relevant subclass table, e.g.

SELECT tblSponsors.SponsorID, tblSponsors.SponsorName,
tblOrganizations.OrgType
FROM tblSponsors INNER JOIN tblOrganizations
ON tblSponsors.SponsorID = tblOrganizations.SponsorID
;



On Fri, 21 Jan 2005 13:31:11 -0800, "Amit"
wrote:

MS Access 2K, Windows XP
====================
Hi,

I have a database with following tables (Primary Key indicated by *) -
1. tblEvent (EventID*, EventName)
2. tblOrganization (OrgID*, OrgName)
3. tblCoalition (CoalitionID*, CoalitionName)
4. tblProgram (ProgramID *, ProgramName)
5. tblCommittee (CommitteeID *, CommitteeName)

An Event can be sponsored by one or more of an Organization, Coalition,
Program or Committee; or any combination (e.g. 2 Organizations and 1 Program
can sponsor an Event together). A sponsoring entity does not exist in more
than one table, for example, an Organization cannot be a Program. So, a Union
query of tables 2, 3, 4 and 5 will be a unique list of names.

I'm not sure how to approach the design of tblEventSponsor to store the
sponsoring entities for the Event.

Approach 1:
=========
tblEventSponsor with the following fields:
1. UniqueID (*) (Autonumber) [I know this is not required, as the other 3
IDs together can work as a Primary Key, but I still like to have a unique ID.]
2. EventID (Foreign Key, from tblEvent)
3. EventSponsorID (Foreign Key, from tables 2, 3, 4 or 5)
4. EventSponsorTypeID

When I design the form, I can have a single sub-form in the Event form to
pick the names of sponsors using a Union query on tables for Organization,
Coalition, Committee and Program to include the respective ID and Name, plus
including an ID for the Sponsor Type (1 = Organization, 2 = Coalition, 3 =
Committee, 4 = Program).
The Union Query will have 3 fields: EventSponsorID, EventSponsorName and
EventSponsorTypeID. I'll hard-code the EventSponsorTypeID in the query.

Approach 2
=========
tblEventSponsorOrganization
1. EventID
2. OrgID

tblEventSponsorCoalition
1. EventID
2. CoalitionID

tblEventSponsorProgram
1. EventID
2. ProgramID

tblEventSponsorCommittee
1. EventID
2. CommitteeID

This would involve creating 4 sub-forms (or 4 combo-boxes), one for each
sponsoring entity.
=============================
I think Approach 1 is better (efficient, less work) than Approach 2, but I
wanted to get some feedback, in case I'm missing something. Are there
advantages to the second approach that I'm not seeing?

Thanks for any advice.

-Amit

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.