table relationship help

[fredk]

I am looking for assistance in setting up correct relationships between 3 tables. (these will be the 3 main tables in my database)

before I outline the tables, one point.... Each "group" is a company that has purchased our product (health insurance) we have a group number which is a 5 digit number that identifies the company. A group may have more than one "product" with us, therefore, we have a sub-group number - for example:

company x has a group number of 12345 - they have two products with us - their full sub-group/group numbers are 00-12345 and 01-12345.

first table is called tblGroups which only contains the main group number field and the group name field.

second table is called tblActivity which has as a primary key the following fields: subgroupnum, groupnum, effectivedate, healthcovcode - this table logs in the sales and also conversions (an example of a conversion would be if someone changes their coverage type - the healthcovcode is a code that represents the specific plan

third table is called tblEfficiency which holds quality information on the paperwork that was submitted. the primary keys are groupnum, effectivedate and datereceived.

For example, we get in a new sale - we assing the group number 12345 - tblgroups will have one entry. The company purchased two types of coverage with us, therefore, there will be two records in tblActivity - one for 00-12345 and one for 01-12345.

Here comes the part I am confused about - I only want one record in the tblEfficiency table for this group. I will need to have sub-forms to link the data and I am having a problem thinking through this last part. How do I link tblActivity to tblEfficiency?

Hope that makes sense.

Thanks!!!

Fred

 

[willir]

Hi Fred

First, I would add a table for Company - you will need this when you run your reports.

Company
CompanyCode - primary key, text
CompanyName

Or use CompanyID if you are using a company ID number elsewhere in your tables.

As I understand it

tblGroup
GroupNum - primary key, text
GroupName

tblActivity
subgroupnum
groupnum
effectivedate
healthcovcode
sales
conversion

subgroupnum, groupnum, effectivedate, healthcovcode - these four fields make up the primary key? Ouch, a little messy, but I can see why.


tblEfficiency
(not sure about the primary key for effeciency - assumption leap)
EffecientID - primary key, numeric
plus fields to capture effeciencies

Now, one thing you have NOT answered is how do you get your SubGroup code - there is not apparent table or field assigned to holding this info for refernce.

You capture this info in your Activity table, but where does SubCode orignate from?

I guess you could capture this in the Group table, or use another table.

Another assumption...

tblSubGroup
GroupNum
SubGroupNum

Primary key is the GroupNum + SubGroupNum

...Moving On

Okay, with the above assumptions, you want to know how to link effeciency to an activity report.

You need to provide more information, or more specifically, answer some questions...

Can an activity report have more than one effeciency rating? For example, suppose you need five reports for the one activity, then you have a One-to-Many (1:M) relationship.

Can an effeciency report be used for various activities? If so, this is also a 1:M relationship.

What I suspect is that you have a number of reports or more specifically, templates that are used for an activity or claim - a medical report, a credit report, a company report, etc...

If this is correct, then you actually have a many to many relationship. This requires an intermediary or joiner table. I like to call them "profiles".

Before creating the profile table, lets review the activity table -- it needs to be tweaked.

tblActivity
ActivityID - primary key
subgroupnum
groupnum
effectivedate
healthcovcode
sales
conversion

Note that you can still assign subgroupnum, groupnum, effectivedate, healthcovcode a multi-field index to prevent duplicates. But to create the profile table, it will be much simpler to achieve this with a simple ActivityID than four fields.

tblReport
ReportID - primary key
Report Name

and, finally...
tblEfficiency
ActivityID
ReportID
EffeciencyScore

Primary key is ActivityID + ReportID

IF you only require ONE report for an activity, then tweak the Activity table

tblActivity
ActivityID - primary key
subgroupnum
groupnum
effectivedate
healthcovcode
sales
conversion
ReportID
EffeciencyScore

Questions?

Hopefully this helped.

Richard

 

[fredk]

Thanks for the detailed reply Richard -

regarding your suggestion to add a Company table - I thought I was achieving this with the group table - the company name and the group number are in that table - the groupnum field is a unique field assigned to the group by our underwriting dept - even if this group changes coverage, the groupnum will remain the same. The subgroupnum is also assigned by underwriting.

I know the primary key tblActivity is a little messy but in order for me to distinguish, thats what I need to do. For example:

We get a new group and assin the group 00-12345 - they purchased coverage code G1234 with an effective date of 1/1/04 - next year (2005) they submit a request to change their coverage... we would have another record in tblActivity with the same subgroupnum and maingroupnum but the effective date and coverage code would be different. (I have another field called n/c which distinguishes between new business and conversions.

Using my same example, each time we receive a group to process... we perform a quality check on the paperwork - we have a listing of errors and track their percentage - for each submission, there would be one record in tblefficiency. Therefore, for group 12345 I would have a record in 2004 and one for 2005. To further complicate this, I can get a group that has two types of coverage - lets say they purchase a PPO plan and an HMO plan... they would have 00-12345 and 01-12345 in the tblactivity table - I only would enter ONE record in tblefficiency for this group event though there are two sub-groups.

there will always be only one activity report for the groupnum/effectivedate/datereceived

Hope this makes sense.

Thanks again!!!

Fred

 

[willir]

Thanks for the info Fred

A normalized approach would suggest to separate the Company from the Group table.

http://www.abcdataworks.com/developers.htm

or

http://r937.com/relational.html

...However, since you only have two companies and a limited amount of groups + subgroups, then I can understand your approach.

You have answered the key question regarding number of reports - one report per activity, and only one effeciency score. You have either a one-to-many, one report template used on more than one activity, or a one-to-one relationship.

IF you only require ONE report for an activity, then tweak the Activity table

tblActivity[tt]
subgroupnum |- Primary key
groupnum |
effectivedate |
healthcovcode |
sales
conversion
ReportID - foreign key to tblReport
EffeciencyScore
+ anything else for the activity ?
[/tt]

Plus the tblReport

Richard