If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How can I customise an entire design set | David | Publisher | 2 | November 12th, 2004 09:43 AM |
Design Templates don't apply font sizes consistantly | Greg H | Powerpoint | 1 | September 15th, 2004 02:07 PM |
design master problem | J. Vermeer | General Discussion | 0 | September 8th, 2004 03:23 PM |
Action queries changing when reopened in design view | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 12:34 AM |
opening a document so it is NOT in design mode | Brad Pears | New Users | 1 | May 3rd, 2004 09:13 PM |