View Single Post
  #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?
.



.