Table relationship - need some simple guidance

[lion3066]

Help, please!

I am creating a database to store information about professional organizations. This information includes, for example, whether the organization offers continuing education (CE)credits, if it accepts CE articles for publication, and if it would allow linking to their website. (Unfortunatel, there are many more categories)

Here’s where I am confused: I don’t know how to properly relate the organization’s information with the many contacts and their information. There is a contact person for each of these subcategories (continuing education, publication acceptance, website linking). For example, Janet So-and-So is the contact for Big Org 1’s continuing education credits.

I do at least have a separate tables for the orgization’s info and the contact’s info. (I am even considering breaking out the org’s subcategories into separate tables; website linking, publications, etc.). BUT, I do not really even know how to relate the organization table to the contact information table.

Is it sufficient that I have located the first (OrgInfo) table's primary key in the second (ContactInfo) table as a foreign key? It does not seem likely b/c I have so many different contacts for each org.

Geeze, how do I even identify the fields for each of these contacts.

Alternatively, could I use a lookup table for each of these contacts, if I uniquely Id'd them? For example, I create a "weblinkContact" filed and set its properties to look-up a person in the ContactInfo table?

Any guidence would get me unstuck!

----------------1st Table----------------------
tbl OrgInfo
orgID = Primary Key
orgName
…
wlAvail = (Y/N) to website linking availability
???(weblink contact)
…
pubAccept – does org accept publications
???(publication contact)
…
???(continuing education contact)


---------------2nd Table----------
tbl ContactInfo
PK – conID
orgID
conName
conTitle
conAddress
…

------------------------------------------

 

[sko]

Tell us more.

Can Janet be the contact for more than one subcategory?
Can Janet be the contact for more than one organization?
Can an organization have more than one contact for any given subcategory?


>Is it sufficient that I have located the first (OrgInfo) table's primary key in the second (ContactInfo) table as a foreign key? It does not seem likely b/c I have so many different contacts for each org.

You're off to a good start here. The fact that you have many contacts for one organization is exactly why you want the foreign key in your contact table. You will have a one to many relationship orgs to contacts.

In the relationship window link tblOrgInfo.orgID to tblContactInfo.orgID

Once this is done, Access will automatically link your contact records with the appropriate organization. How? Picture a form/subform. The main form is where you enter the organization. The subform is in datasheet view (many rows and columns, just like your contact table). As you enter the many contacts for that specific organization Access will copy that organizations ID into your contact record for you. Have you worked with forms/subforms?

 

[lion3066]

Thanks for responding. I have provided answers to the questions you posed.

>Can Janet be the contact for more than one subcategory?
Yes. Janet may, in fact, be contact for most of the subcategories. Yet, one or two other people may be contacts for the remaining subcategories.

>Can Janet be the contact for more than one organization?
No, probably not. I have not run across this yet.

>Can an organization have more than one contact for any given subcategory?
No, or at least I have the power to designate only one contact per subcategory.


>Picture a form/subform. … As you enter the many contacts for that specific organization Access will copy that organizations ID into your contact record for you. Have you worked with forms/subforms?
Honestly, I have only tinkered with forms but I was hoping that this was a possible solution to having the user enter in the organization’s data in a logically coherent manner.

Question: Should I use a separate table for each subcategory (continuing education, publication acceptance, website linking) with both “orgID” and “conID” being foreign keys? Currently, I am wrestling with the fact that I put each subcategory in the “OrgInfo” table.

I don’t mean to be dense. Maybe this question is moot, if I just structure my form/subform properly

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Proposed solution to question à
----------------1st Table-------Organization Information---------------
tbl OrgInfo
PK - orgID
orgName
…

---------------2nd Table--------Contact Information
tbl ContactInfo
PK – conID
FK - orgID
conName
conTitle
conAddress
…

---------3rd Table------------Website Linking Information
PK – wlID
FK – orgID
FK - conID
wlAvail
wlProced

---------Nth Table(s)-----------Coninuing Education Information, etc.
PK – ceaID
FK- orgID
FK – conID
ceaAvail
ceaPoced
…

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Existing structure à
----------------1st Table-------Organization Information---------------
tbl OrgInfo
PK - orgID
FK - conID
orgName
… <weblink subcategory fields>
wlAvail
wlProced
…<continuing education subcategory fields>
ceaAvail
ceaProced

---------------2nd Table--------Contact Information
tbl ContactInfo
PK – conID
FK - orgID
conName
conTitle
conAddress
…
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[sko]

I agree that you need a separate table for subcategories but I don't think you'd want one for each category. So far it looks like you're collecting the same type of information for each category. You want to know if it's available. I think you need to move away from the y/n fields and concentrate on just listing what is available at each organization. Here's how I see it:

----------tblCategories------------

This is a list of all subCategories that may or may not be available at a particular organization. It is used as the record source for a combo box.

catID =PK I am going to use a text abbreviation
description = This is the full text

Data Example:
CEA Offers continuing education
WEB Web linking available
PUB Accepts CE articles for publication

--------------tblOrganizations-------------------

orgID =PK Autonumber
orgName

Data Example:
1 Big Company
2 Bigger Company

----------------tblContacts-----------------------

conID =PK Autonumber
Name
Title
etc.
orgID =FK Long Integer

Data Example
2 Janet President 1
3 Jim President 2

---------------tblSubCategories--------------------

This is the table that stores the &quot;subcategories&quot; available at each specific organization along with the contact for that category.

RecordNum =PK Autonumber
CatID = FK The combobox is tied to this field
OrgID = FK
conID = FK

Data Example:

1 CEA 1 2
2 WEB 1 2
3 CEA 2 3

Translation:
Big Company offers Continuing Education Credits, contact Janet.
Big Company offers Web Linking, contact Janet.
Bigger Company offers Continuing Education Credits, contact Jim.

---------------Relationships---------------
One to Many
tblOrganizations.orgID to tblContacts.orgID

One to Many
tblOrganizations.orgID to tblSubCategories.orgID

One to Many
tblContacts.conID to tblSubCategories.conID

One to Many
tblCategories.catID to tblSubCategories.catID

 

[lion3066]

I see your point. To wrap my head around it, let me just ask this LASTset of questions.REALLY!!!

So, for the details of availability that appear in most subcategories, I could just extrapolate from your construction. I could just create a “Process Table” with the relevant fields: Process, Fee, Timeframe, and Formatting. Many but not all subcategories require these generic fields. I would tie it to the SubCategories table as follows:
-----tblProcess--------
proID = PK autonumber
Process
Fee
Timeframe
Format
orgID =FK

-----tblSubCategories-------
RecordNum =PK Autonumber
CatID = FK w/combo box . . .
OrgID = FK
conID = FK
proID = FK


What about the oddball fields, those elements of information specific to only one subcategory? These fields, for example, include sorting capability for mailing labels or frequency and attendance numbers for continuing education conferences.

Do I create separate tables for these field groupings (a table for frequency and attendance and another just for the sorting field)? Or, would you just lump them into the “Process Table” outlined above?

Truly and Honestly –Thank You– for all of your help

 

[sko]

Ok, now we get the rest of the story

First of all, could you give me some data examples for tblProcess. I need to see how this fits in with your subcategories. I suspect it's some kind of class/conference schedule.

Big Company offers continuing education classes as follows:
??

If that's the case, then tblProcess would hold all the individual dates/times for &quot;many of the subcategories&quot; for &quot;many of the organizations&quot; (a relationship you can't determine by linking tblProcess.orgID to tblSubCategories.orgID).

What kind of sorting capability do you need that would call for additional fields?

 

[lion3066]

I was just trying not to bore you with the gory details

>… could you give me some data examples for tblProcess. I need to see how this fits in with your subcategories. …

tblProcess has only four fields: process, timeframe, fee & format.

The data would vary by subcategory (but the examples I have don’t seem to vary too much). There are 11 subcategories but only 7 that use the four fields in the tblProcess. Of these 7, 4 use only two fields (process & timeframe) and the other 3 use the remaining fields (fee & format).

The remaining 4 subcategories do not use any of these four fields, so I will just create separate tables for them.

Here are some examples of data by subcategory:
Subcategory –
Exhibit Opportunities
Process – Contact John Doe two months before conference. He will supply you with further details.
TimeFrame – 2 month lead-time necessary.
Fee – $550 per
Format – N/A

Cont. Ed. Accreditation
Process – Accrediting body is the National Association of Blank. Materials must be sent this association for review.
TimeFrame – 1 month turn around.
Fee – N/A
Format – N/A

Mailing Labels
Process – Contact Linda Doe for details
TimeFrame – 1 week turn around
Fee - $75
Format – N/A

E-mail Distribution List
Process – No established procedure. Contact Trudy Doe for details.
TimeFrame – Unknown.
Fee – Free for NonProfit
Format – Unknown. Contact Trudy Doe for details


>What kind of sorting capability do you need that would call for additional fields?

Really, none beyond those fields I have given; unless I am missing your point. I can’t get my supervisor to say anything more than – “Well geeze, we were going to just put all this data into an Excel spreadsheet so anything you give us will be great!” I think she thinks that is supposed to make me feel better but it doesn't b/c I don’t want to give her an Access (read, &quot;complex&quot version of the same thing (a flat file database).
So, I have tried to make minor adjustments for foreseeable uses (and sorting). One example, is that all of her “process” data contained “time frame” information (see the Exhibit Oppty example above). I figured she might want to sort by time frame and so I created a separate field.
Another example, is the solution you have kindly proposed to creating a single table with a separate field for each subcategory contact (or process details).
Thanks

 

[sko]

From your data examples, it looks like you have a one to one relationship between tblProcess and tblSubCategories. Correct? One unique organization/subcategory combination will have one process record.

Usually a 1 to 1 means the data should be combined into a single table but you may want to keep the split if the &quot;process&quot; relates to a small percentage of your records in tblSubCategories. If you choose to keep the tables separate make these changes:

--------------tblProcess-----------
proID = PK Long Integer
Process
Fee
TimeFormat


Leave out orgID. It's already in tblSubcategories.

--------------Relationship-----------
One to One
tblProcess.proID to tblSubcategories.recordnum

Now for a couple suggestions

Making TimeFormat a number field will make it more useful for querying and sorting. I'd enter time in days (7, 30, 60). Imagine if you needed to find the organizations that have a turn around time of 1 month or less. You can always add a label in your form &quot;Lead-Time in Days&quot; for clarification.

I'd also add a multi-field index in tblSubCategories using orgID and catID. Each organization should have only one record per category in tblSubCategories. This will prevent duplicate records.

HTH

 

[lion3066]

SKO - Thanks for your help. Lion3066