A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Avoiding Redundant Records



 
 
Thread Tools Display Modes
  #1  
Old January 21st, 2010, 07:27 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Avoiding Redundant Records

It is my understanding that surrogate keys are generally recommended to
ensure uniqueness of records. Is it not true that using surrogate keys
implies taking extra precautions to prevent duplicate records? I mean, with
surrogate keys there is nothing to prevent the proliferation of multiple
records all containing the same data, but each having a unique key.

I would appreciate your help with this in the following context:

AGREEMENTS
AgrmtID (PK)
InsuredID
Agrmt fields…

CERTS
CertID (PK)
AgrmtID
ProducerID
Cert fields…

POLICIES
PolicyID (PK)
InsuredID
PolicyTypeCode
ProviderID
Policy fields…

CERTSPOLICIES
CertsPoliciesID (PK)
CertID
PolicyID

Note: Any fieldname ending in “ID” is a surrogate key.

An Agreement can have zero or more Certs; a Cert pertains to only one
Agreement, so this is a one-to-many relationship. Each Cert can have one or
more Policies; the same Policy can be on different Certs, so this is a
many-to-many relationship, hence these two tables are joined by the
CertsPolicies table.

We don’t want the same Policy to appear more than once on the same Cert. I
believe this can be accomplished fairly easily by setting up CertID and
PolicyID as a multi-field unique index in the junction table.

We also have to ensure that the user doesn’t inadvertently relate any one
Policy twice to the same Agreement through the use of a second Cert. In
other words, we do not want to see the same Policy on two different Certs for
the same Agreement. How would this be accomplished?

A fundamental assumption is that no Insured will ever have more than one
Policy of a given type. How would I guarantee that not more than one Policy
of a given type (PolicyType Code) ever appears on any Cert? How would I
guarantee the same thing for any two Certs assigned to the same Insured?

Thanks,
OldBlindPew

Ads
  #2  
Old January 21st, 2010, 09:17 PM posted to microsoft.public.access.tablesdbdesign
Piet Linden[_2_]
external usenet poster
 
Posts: 280
Default Avoiding Redundant Records

On Jan 21, 12:27*pm, oldblindpew
wrote:
It is my understanding that surrogate keys are generally recommended to
ensure uniqueness of records. *Is it not true that using surrogate keys
implies taking extra precautions to prevent duplicate records? *I mean, with
surrogate keys there is nothing to prevent the proliferation of multiple
records all containing the same data, but each having a unique key.

I would appreciate your help with this in the following context:

AGREEMENTS
AgrmtID (PK)
InsuredID
Agrmt fields

CERTS
CertID (PK)
AgrmtID
ProducerID
Cert fields

POLICIES
PolicyID (PK)
InsuredID
PolicyTypeCode
ProviderID
Policy fields

CERTSPOLICIES
CertsPoliciesID (PK)
CertID
PolicyID

Note: *Any fieldname ending in ID is a surrogate key.

An Agreement can have zero or more Certs; a Cert pertains to only one
Agreement, so this is a one-to-many relationship. *Each Cert can have one or
more Policies; the same Policy can be on different Certs, so this is a
many-to-many relationship, hence these two tables are joined by the
CertsPolicies table.

We dont want the same Policy to appear more than once on the same Cert.. *I
believe this can be accomplished fairly easily by setting up CertID and
PolicyID as a multi-field unique index in the junction table.

We also have to ensure that the user doesnt inadvertently relate any one
Policy twice to the same Agreement through the use of a second Cert. *In
other words, we do not want to see the same Policy on two different Certs for
the same Agreement. *How would this be accomplished?

A fundamental assumption is that no Insured will ever have more than one
Policy of a given type. *How would I guarantee that not more than one Policy
of a given type (PolicyType Code) ever appears on any Cert? *How would I
guarantee the same thing for any two Certs assigned to the same Insured?

Thanks,
OldBlindPew


You could create a unique index on the combination of (CertID,
PolicyID) in the CertsPolicies table. Nothing wrong with that. Then
if your CertsPoliciesID is an autonumber and set to be unique, you
should have everything, right?
  #3  
Old January 21st, 2010, 11:53 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Avoiding Redundant Records

CertsPoliciesID is autonumber and therefore the unique primary key for the
junction table. A unique index on the combination of CertID and PolicyID
would prevent redundant Cert/Policy pairs.

But I am also concerned with redundant Agreement/Policy pairs. It is
acceptable for an Agreement to have more than one Cert, but not that the same
Policy should appear on more than one of their Certs. Enforcing Cert/Policy
uniqueness alone doesn't prevent this, and the uniqueness of the
CertsPoliciesID key adds nothing.

Similarly, I am concerned to prevent improper combinations resulting from
policy types. No Insured party is going to carry two General Liability
Policies. If we try to attribute two different GL policies to the same
Insured, either by assigning the two policies to the same Cert, or by
assigning them to two different Certs that are in turn tied to the same
Agreement, something is wrong.

Thanks,
oldblindpew

"Piet Linden" wrote:

You could create a unique index on the combination of (CertID,
PolicyID) in the CertsPolicies table. Nothing wrong with that. Then
if your CertsPoliciesID is an autonumber and set to be unique, you
should have everything, right?
.

  #4  
Old January 22nd, 2010, 01:35 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Avoiding Redundant Records

It sounds like you are describing the "business rules" of your operation.
It wouldn't matter if you were using Access or Excel or paper and pencil,
those rules would apply (e.g., no customer carries more than one GL policy).

I'm not aware of any built-in business rule enforcer in MS Access. I
believe you'll need to add the validation checks to enforce those rules.

In some of your situations, using a unique index on multiple fields could be
a way to use Access features to enforce your business rules ... but that's
just plain lucky! You'll probably need to figure out some edits/validation
tests for your form, to prevent the users from doing something your business
doesn't permit.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
CertsPoliciesID is autonumber and therefore the unique primary key for the
junction table. A unique index on the combination of CertID and PolicyID
would prevent redundant Cert/Policy pairs.

But I am also concerned with redundant Agreement/Policy pairs. It is
acceptable for an Agreement to have more than one Cert, but not that the
same
Policy should appear on more than one of their Certs. Enforcing
Cert/Policy
uniqueness alone doesn't prevent this, and the uniqueness of the
CertsPoliciesID key adds nothing.

Similarly, I am concerned to prevent improper combinations resulting from
policy types. No Insured party is going to carry two General Liability
Policies. If we try to attribute two different GL policies to the same
Insured, either by assigning the two policies to the same Cert, or by
assigning them to two different Certs that are in turn tied to the same
Agreement, something is wrong.

Thanks,
oldblindpew

"Piet Linden" wrote:

You could create a unique index on the combination of (CertID,
PolicyID) in the CertsPolicies table. Nothing wrong with that. Then
if your CertsPoliciesID is an autonumber and set to be unique, you
should have everything, right?
.



  #5  
Old January 22nd, 2010, 01:55 AM posted to microsoft.public.access.tablesdbdesign
Piet Linden[_2_]
external usenet poster
 
Posts: 280
Default Avoiding Redundant Records

On Jan 21, 4:53*pm, oldblindpew
wrote:
CertsPoliciesID is autonumber and therefore the unique primary key for the
junction table. *A unique index on the combination of CertID and PolicyID
would prevent redundant Cert/Policy pairs.

But I am also concerned with redundant Agreement/Policy pairs. *It is
acceptable for an Agreement to have more than one Cert, but not that the same
Policy should appear on more than one of their Certs. *Enforcing Cert/Policy
uniqueness alone doesn't prevent this, and the uniqueness of the
CertsPoliciesID key adds nothing. *

Similarly, I am concerned to prevent improper combinations resulting from
policy types. *No Insured party is going to carry two General Liability
Policies. *If we try to attribute two different GL policies to the same
Insured, either by assigning the two policies to the same Cert, or by
assigning them to two different Certs that are in turn tied to the same
Agreement, something is wrong.

Thanks,
oldblindpew

"Piet Linden" wrote:
You could create a unique index on the combination of (CertID,
PolicyID) in the CertsPolicies table. *Nothing wrong with that. *Then
if your CertsPoliciesID is an autonumber and set to be unique, you
should have everything, right?
.


Another way of doing the validation is in the BeforeInsert event of
the form. You could do the checks there and if no rules are violated,
allow the insert. Other than that, I'm out of ideas.
  #6  
Old January 22nd, 2010, 06:18 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Avoiding Redundant Records

I think I see your point, although at first reading I was a bit dumbfounded.
Conversation via email can be so difficult. At first it sounded like you
were surprised I was actually trying to design my application around business
rules! Further, that it was going to be up to my users, not my application,
to enforce our business rules. Finally, it sounded like you were saying that
if any Access features proved helpful in this task, it would be purely
accidental!

I believe you were actually saying that, right offhand, there is nothing I
can do to the structure of my tables or their relationships to prevent
unwanted records of the sorts I described. Rather, these illegal operations
must be prevented by traps in my code or by using data validation rules.

A perhaps easier example would be in retail sales. Let's say we offered a
product for sale with the condition: limit one per customer. This would mean
that for any instance of this product in the OrdersProducts join table, the
Quantity would have to be limited to 1 each. Also, we would have to prohibit
multiple separate instances of the same product on the same order. Further,
we would have to prevent multiple orders for the same product from the same
customer. These kinds of constraints would not be enforced through table
structure, except to the extent of making sure we placed a field to our
Products table for flagging such products.

Regards,
OldBlindPew

"Jeff Boyce" wrote:

It sounds like you are describing the "business rules" of your operation.
It wouldn't matter if you were using Access or Excel or paper and pencil,
those rules would apply (e.g., no customer carries more than one GL policy).

I'm not aware of any built-in business rule enforcer in MS Access. I
believe you'll need to add the validation checks to enforce those rules.

In some of your situations, using a unique index on multiple fields could be
a way to use Access features to enforce your business rules ... but that's
just plain lucky! You'll probably need to figure out some edits/validation
tests for your form, to prevent the users from doing something your business
doesn't permit.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
CertsPoliciesID is autonumber and therefore the unique primary key for the
junction table. A unique index on the combination of CertID and PolicyID
would prevent redundant Cert/Policy pairs.

But I am also concerned with redundant Agreement/Policy pairs. It is
acceptable for an Agreement to have more than one Cert, but not that the
same
Policy should appear on more than one of their Certs. Enforcing
Cert/Policy
uniqueness alone doesn't prevent this, and the uniqueness of the
CertsPoliciesID key adds nothing.

Similarly, I am concerned to prevent improper combinations resulting from
policy types. No Insured party is going to carry two General Liability
Policies. If we try to attribute two different GL policies to the same
Insured, either by assigning the two policies to the same Cert, or by
assigning them to two different Certs that are in turn tied to the same
Agreement, something is wrong.

Thanks,
oldblindpew

"Piet Linden" wrote:

You could create a unique index on the combination of (CertID,
PolicyID) in the CertsPolicies table. Nothing wrong with that. Then
if your CertsPoliciesID is an autonumber and set to be unique, you
should have everything, right?
.



.

  #7  
Old January 22nd, 2010, 10:32 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Avoiding Redundant Records

Sorry if I gave you a start, there. Yes, Access (and many other tools,
including Excel, paper/pencil, etc.) can be used to handle business rules
.... BUT! ... you have to do most of the work the handle the rules, using the
features/functions of your tool.

Your example (retail sales, limit: one per customer) is excellent. While
there may be nothing built in to prevent many of those situations, you can
certainly add in "traps" (?validation checks) to accomplish that. Or, if
you're lucky, using something like a unique index (again, a feature of your
tool) might help you reinforce the business rule. You still have to set the
unique index, though!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
I think I see your point, although at first reading I was a bit
dumbfounded.
Conversation via email can be so difficult. At first it sounded like you
were surprised I was actually trying to design my application around
business
rules! Further, that it was going to be up to my users, not my
application,
to enforce our business rules. Finally, it sounded like you were saying
that
if any Access features proved helpful in this task, it would be purely
accidental!

I believe you were actually saying that, right offhand, there is nothing I
can do to the structure of my tables or their relationships to prevent
unwanted records of the sorts I described. Rather, these illegal
operations
must be prevented by traps in my code or by using data validation rules.

A perhaps easier example would be in retail sales. Let's say we offered a
product for sale with the condition: limit one per customer. This would
mean
that for any instance of this product in the OrdersProducts join table,
the
Quantity would have to be limited to 1 each. Also, we would have to
prohibit
multiple separate instances of the same product on the same order.
Further,
we would have to prevent multiple orders for the same product from the
same
customer. These kinds of constraints would not be enforced through table
structure, except to the extent of making sure we placed a field to our
Products table for flagging such products.

Regards,
OldBlindPew

"Jeff Boyce" wrote:

It sounds like you are describing the "business rules" of your operation.
It wouldn't matter if you were using Access or Excel or paper and pencil,
those rules would apply (e.g., no customer carries more than one GL
policy).

I'm not aware of any built-in business rule enforcer in MS Access. I
believe you'll need to add the validation checks to enforce those rules.

In some of your situations, using a unique index on multiple fields could
be
a way to use Access features to enforce your business rules ... but
that's
just plain lucky! You'll probably need to figure out some
edits/validation
tests for your form, to prevent the users from doing something your
business
doesn't permit.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
CertsPoliciesID is autonumber and therefore the unique primary key for
the
junction table. A unique index on the combination of CertID and
PolicyID
would prevent redundant Cert/Policy pairs.

But I am also concerned with redundant Agreement/Policy pairs. It is
acceptable for an Agreement to have more than one Cert, but not that
the
same
Policy should appear on more than one of their Certs. Enforcing
Cert/Policy
uniqueness alone doesn't prevent this, and the uniqueness of the
CertsPoliciesID key adds nothing.

Similarly, I am concerned to prevent improper combinations resulting
from
policy types. No Insured party is going to carry two General Liability
Policies. If we try to attribute two different GL policies to the same
Insured, either by assigning the two policies to the same Cert, or by
assigning them to two different Certs that are in turn tied to the same
Agreement, something is wrong.

Thanks,
oldblindpew

"Piet Linden" wrote:

You could create a unique index on the combination of (CertID,
PolicyID) in the CertsPolicies table. Nothing wrong with that. Then
if your CertsPoliciesID is an autonumber and set to be unique, you
should have everything, right?
.



.



  #8  
Old January 25th, 2010, 05:38 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Avoiding Redundant Records

Not meaning to sound ungrateful, but the clear purpose of my original post
was to ask for suggestions on how to use the features and functions of Access
to implement certain specific business rules in my application. We seem to
have circumnavigated the barn only to arrive at the door. Folks were eager
to tell me I needed to use surrogate keys and to normalize my tables, but
when I ask about some of the basic ramifications of those choices, I often
get vague replies and shrugs of shoulders.

After drawing a blank here, I scabbed onto another recent thread entitled
"Multi-Field Primary Keys", which deals with this same subject, and got a
reply indicating there are indeed specific ways I can set up table
relationships using multi-field indexes that might prove helpful. The
response was perhaps a bit over my head, and this in itself is probably the
crux of the matter.

Designing an Access application is not easy, and there is only so much
people can do on a voluntary basis, from a distance. There are many ways to
go about it, and even experts sometimes disagree on the best approach.
Ultimately, designing an application is not something that can be done well
by someone "driving from the back seat". Please know that I appreciate your
interest and help. It appears I just need to continue slogging on in the
slow and painful business of gaining wisdom by trial and error.

With Sincere Thanks,
OldBlindPew

"Jeff Boyce" wrote:

Sorry if I gave you a start, there. Yes, Access (and many other tools,
including Excel, paper/pencil, etc.) can be used to handle business rules
.... BUT! ... you have to do most of the work the handle the rules, using the
features/functions of your tool.

Your example (retail sales, limit: one per customer) is excellent. While
there may be nothing built in to prevent many of those situations, you can
certainly add in "traps" (?validation checks) to accomplish that. Or, if
you're lucky, using something like a unique index (again, a feature of your
tool) might help you reinforce the business rule. You still have to set the
unique index, though!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
I think I see your point, although at first reading I was a bit
dumbfounded.
Conversation via email can be so difficult. At first it sounded like you
were surprised I was actually trying to design my application around
business
rules! Further, that it was going to be up to my users, not my
application,
to enforce our business rules. Finally, it sounded like you were saying
that
if any Access features proved helpful in this task, it would be purely
accidental!

I believe you were actually saying that, right offhand, there is nothing I
can do to the structure of my tables or their relationships to prevent
unwanted records of the sorts I described. Rather, these illegal
operations
must be prevented by traps in my code or by using data validation rules.

A perhaps easier example would be in retail sales. Let's say we offered a
product for sale with the condition: limit one per customer. This would
mean
that for any instance of this product in the OrdersProducts join table,
the
Quantity would have to be limited to 1 each. Also, we would have to
prohibit
multiple separate instances of the same product on the same order.
Further,
we would have to prevent multiple orders for the same product from the
same
customer. These kinds of constraints would not be enforced through table
structure, except to the extent of making sure we placed a field to our
Products table for flagging such products.

Regards,
OldBlindPew

"Jeff Boyce" wrote:

It sounds like you are describing the "business rules" of your operation.
It wouldn't matter if you were using Access or Excel or paper and pencil,
those rules would apply (e.g., no customer carries more than one GL
policy).

I'm not aware of any built-in business rule enforcer in MS Access. I
believe you'll need to add the validation checks to enforce those rules.

In some of your situations, using a unique index on multiple fields could
be
a way to use Access features to enforce your business rules ... but
that's
just plain lucky! You'll probably need to figure out some
edits/validation
tests for your form, to prevent the users from doing something your
business
doesn't permit.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
CertsPoliciesID is autonumber and therefore the unique primary key for
the
junction table. A unique index on the combination of CertID and
PolicyID
would prevent redundant Cert/Policy pairs.

But I am also concerned with redundant Agreement/Policy pairs. It is
acceptable for an Agreement to have more than one Cert, but not that
the
same
Policy should appear on more than one of their Certs. Enforcing
Cert/Policy
uniqueness alone doesn't prevent this, and the uniqueness of the
CertsPoliciesID key adds nothing.

Similarly, I am concerned to prevent improper combinations resulting
from
policy types. No Insured party is going to carry two General Liability
Policies. If we try to attribute two different GL policies to the same
Insured, either by assigning the two policies to the same Cert, or by
assigning them to two different Certs that are in turn tied to the same
Agreement, something is wrong.

Thanks,
oldblindpew

"Piet Linden" wrote:

You could create a unique index on the combination of (CertID,
PolicyID) in the CertsPolicies table. Nothing wrong with that. Then
if your CertsPoliciesID is an autonumber and set to be unique, you
should have everything, right?
.



.



.

  #9  
Old January 26th, 2010, 04:38 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Avoiding Redundant Records

It sounds as if you are on the right track in a lot of ways. You are
absolutely correct that a surrogate key does not guarantee uniqueness of
other data in the record. For that you need to use multi-field indexes
and/or form-level validation, as it seems you understand.

As a point of terminology, you may do better not to think of your linking
fields as surrogate keys. The combination of "real world" fields is what
guarantees a unique record, as you clearly understand. The surrogate key
just provides a one-field representation of the unique record, in the same
way an Employee # identifies the employee uniquely in a single field. The
linking fields, while they may not be "real" (i.e. they are arbitrary numbers)
derive their values from the surrogate key of the parent table, rather than
being surrogate keys themselves. In any case, they are not surrogate primary
keys.

As I understand, you could have for an Agreement the following:

AgreementID = 1000
Cert 100
Policy A
Policy B

Cert 200
Policy C
Policy D

You want to assure Policy A is not available for Cert 200 under the umbrella
of Agreement 1000, yes? If so, you do not have an index available to enforce
that rule (unless I am missing something) because the Policy record does not
have direct access to AgreementID. Instead, you need to use other
capabilities of Access to enforce that rule.

If the Policy is selected from a combo box on the subform bound to the
junction table, it should be possible to devise a query for the Row Source
that excludes policies already selected. I do not have time to devise an
example just now, and in any case I am not yet skilled enough at queries to
be sure I am pointing you in the right direction. That said, the logic would
probably be to select Policies from tblPolicy where PolicyID has not already
been used in the current Agreement record.

I wish I could be of more help. I have been watching this thread to see what
is suggested, as the problem interests me and I do not have a ready solution,
but as it has been a while since there have been any postings I am jumping in
to steer you away from an index-based solution, which I do not think you will
find for this situation. I expect the solution is SQL-based, particularly if
you are setting the Row Source for a combo box. A SQL string can be devised
in VBA to set the Row Source. If you wish to experiment further you could
try devising a query that returns all of the Policies used for an agreement.
This would probably involve joining the junction table to tblCert to
tblAgreement, restricting the tblAgreement record to just one. You could
hard-code the criteria for AgreementID for starters. That is, for a given
record make note of the number, then use that number as the AgreementID
criteria. You should be able to return a list of Policies used for that
Agreement. If you can do that you have made a good start.

Best suggestion other than that is to start a new thread. Perhaps the Forms
Programming group would be a good choice, or maybe the Queries group. A new
thread is likely to attract more attention to one that is several responses
deep.


oldblindpew wrote:
Not meaning to sound ungrateful, but the clear purpose of my original post
was to ask for suggestions on how to use the features and functions of Access
to implement certain specific business rules in my application. We seem to
have circumnavigated the barn only to arrive at the door. Folks were eager
to tell me I needed to use surrogate keys and to normalize my tables, but
when I ask about some of the basic ramifications of those choices, I often
get vague replies and shrugs of shoulders.

After drawing a blank here, I scabbed onto another recent thread entitled
"Multi-Field Primary Keys", which deals with this same subject, and got a
reply indicating there are indeed specific ways I can set up table
relationships using multi-field indexes that might prove helpful. The
response was perhaps a bit over my head, and this in itself is probably the
crux of the matter.

Designing an Access application is not easy, and there is only so much
people can do on a voluntary basis, from a distance. There are many ways to
go about it, and even experts sometimes disagree on the best approach.
Ultimately, designing an application is not something that can be done well
by someone "driving from the back seat". Please know that I appreciate your
interest and help. It appears I just need to continue slogging on in the
slow and painful business of gaining wisdom by trial and error.

With Sincere Thanks,
OldBlindPew

Sorry if I gave you a start, there. Yes, Access (and many other tools,
including Excel, paper/pencil, etc.) can be used to handle business rules

[quoted text clipped - 109 lines]

.


--
Message posted via http://www.accessmonster.com

  #10  
Old January 27th, 2010, 12:42 AM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Avoiding Redundant Records

Thanks for your response.

Yes, your point is well taken, assuming I understood it correctly, that the
term "surrogate" makes sense only when applied to a primary key. (I had said
in my OP that any field ending in "ID" was a surrogate key). When the same
field name appears in another table, it is no longer a surrogate key, but
simply the foreign key linking that table to the parent table. The fact is,
I did not know how else to say what I was trying to say. I didn't want to
say "autonumber", for the same reason, i.e., only the primary key is truly
autonumbered.

You are correct in your grasp of the relationships between Agreements,
Certs, and Policies. If Agreement 1000 has Cert 100, which in turn has
Policies A and B, and if Agreement 1000 also has Cert 200, then we do not
want Policy A to appear on Cert 200. Nor would we want any other Policy OF
THE SAME TYPE as Policy A to appear on Cert 200.

If things weren't already complicated enough, there is another level below
Agreements, Certs, and Policies, which is the Coverage Details. If I can
figure out how to manage the first three levels, the fourth will hopefully
not present any essential complications. Well..., except for the fact as
mentioned in another thread, that I will have to use one field to hold values
of different data types. I believe this means I will have to settle on one
data type, say Number, and then add another field to flag the value for what
it is really meant to be.

You are also correct about the Policy record not having direct access to the
AgreementID. Since the same Insured firm, with the same Policy, can enter
into more than one Agreement, the AgreementID cannot be built into the Policy
record.

The common fact about Agreements, Certs, and Policies, is the Insured firm.
I have added the InsuredID to the Certs table. When setting up a new Cert or
Certs for a new Agreement, the user should be presented with a list of
existing policies for the Insured; there should not be more than one of each
PolicyType. The user should be able to select policies for the new Cert, and
see them change from "available" to "already present on another Cert". Of
course for the Insured's very first Agreement, his Policies would have to be
added to the Policies table before they could be associated with any Cert.

Thanks Again,
OBP


"BruceM via AccessMonster.com" wrote:

It sounds as if you are on the right track in a lot of ways. You are
absolutely correct that a surrogate key does not guarantee uniqueness of
other data in the record. For that you need to use multi-field indexes
and/or form-level validation, as it seems you understand.

As a point of terminology, you may do better not to think of your linking
fields as surrogate keys. The combination of "real world" fields is what
guarantees a unique record, as you clearly understand. The surrogate key
just provides a one-field representation of the unique record, in the same
way an Employee # identifies the employee uniquely in a single field. The
linking fields, while they may not be "real" (i.e. they are arbitrary numbers)
derive their values from the surrogate key of the parent table, rather than
being surrogate keys themselves. In any case, they are not surrogate primary
keys.

As I understand, you could have for an Agreement the following:

AgreementID = 1000
Cert 100
Policy A
Policy B

Cert 200
Policy C
Policy D

You want to assure Policy A is not available for Cert 200 under the umbrella
of Agreement 1000, yes? If so, you do not have an index available to enforce
that rule (unless I am missing something) because the Policy record does not
have direct access to AgreementID. Instead, you need to use other
capabilities of Access to enforce that rule.

If the Policy is selected from a combo box on the subform bound to the
junction table, it should be possible to devise a query for the Row Source
that excludes policies already selected. I do not have time to devise an
example just now, and in any case I am not yet skilled enough at queries to
be sure I am pointing you in the right direction. That said, the logic would
probably be to select Policies from tblPolicy where PolicyID has not already
been used in the current Agreement record.

I wish I could be of more help. I have been watching this thread to see what
is suggested, as the problem interests me and I do not have a ready solution,
but as it has been a while since there have been any postings I am jumping in
to steer you away from an index-based solution, which I do not think you will
find for this situation. I expect the solution is SQL-based, particularly if
you are setting the Row Source for a combo box. A SQL string can be devised
in VBA to set the Row Source. If you wish to experiment further you could
try devising a query that returns all of the Policies used for an agreement.
This would probably involve joining the junction table to tblCert to
tblAgreement, restricting the tblAgreement record to just one. You could
hard-code the criteria for AgreementID for starters. That is, for a given
record make note of the number, then use that number as the AgreementID
criteria. You should be able to return a list of Policies used for that
Agreement. If you can do that you have made a good start.

Best suggestion other than that is to start a new thread. Perhaps the Forms
Programming group would be a good choice, or maybe the Queries group. A new
thread is likely to attract more attention to one that is several responses
deep.


oldblindpew wrote:
Not meaning to sound ungrateful, but the clear purpose of my original post
was to ask for suggestions on how to use the features and functions of Access
to implement certain specific business rules in my application. We seem to
have circumnavigated the barn only to arrive at the door. Folks were eager
to tell me I needed to use surrogate keys and to normalize my tables, but
when I ask about some of the basic ramifications of those choices, I often
get vague replies and shrugs of shoulders.

After drawing a blank here, I scabbed onto another recent thread entitled
"Multi-Field Primary Keys", which deals with this same subject, and got a
reply indicating there are indeed specific ways I can set up table
relationships using multi-field indexes that might prove helpful. The
response was perhaps a bit over my head, and this in itself is probably the
crux of the matter.

Designing an Access application is not easy, and there is only so much
people can do on a voluntary basis, from a distance. There are many ways to
go about it, and even experts sometimes disagree on the best approach.
Ultimately, designing an application is not something that can be done well
by someone "driving from the back seat". Please know that I appreciate your
interest and help. It appears I just need to continue slogging on in the
slow and painful business of gaining wisdom by trial and error.

With Sincere Thanks,
OldBlindPew

Sorry if I gave you a start, there. Yes, Access (and many other tools,
including Excel, paper/pencil, etc.) can be used to handle business rules

[quoted text clipped - 109 lines]

.


--
Message posted via http://www.accessmonster.com

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.