Avoiding Redundant Records

Discussion in 'Access Table Design' started by oldblindpew, Jan 21, 2010.

  1. oldblindpew

    oldblindpew Guest

    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
     
    oldblindpew, Jan 21, 2010
    #1
    1. Advertisements

  2. oldblindpew

    Piet Linden Guest

    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 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 Certsfor
    > 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?
     
    Piet Linden, Jan 21, 2010
    #2
    1. Advertisements

  3. oldblindpew

    oldblindpew Guest

    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?
    > .
    >
     
    oldblindpew, Jan 21, 2010
    #3
  4. oldblindpew

    Jeff Boyce Guest

    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
    news:...
    > 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?
    >> .
    >>
     
    Jeff Boyce, Jan 22, 2010
    #4
  5. oldblindpew

    Piet Linden Guest

    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.
     
    Piet Linden, Jan 22, 2010
    #5
  6. oldblindpew

    oldblindpew Guest

    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
    > news:...
    > > 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?
    > >> .
    > >>

    >
    >
    > .
    >
     
    oldblindpew, Jan 22, 2010
    #6
  7. oldblindpew

    Jeff Boyce Guest

    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
    news:...
    >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
    >> news:...
    >> > 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?
    >> >> .
    >> >>

    >>
    >>
    >> .
    >>
     
    Jeff Boyce, Jan 22, 2010
    #7
  8. oldblindpew

    oldblindpew Guest

    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
    > news:...
    > >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
    > >> news:...
    > >> > 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?
    > >> >> .
    > >> >>
    > >>
    > >>
    > >> .
    > >>

    >
    >
    > .
    >
     
    oldblindpew, Jan 25, 2010
    #8
  9. 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]
    >>
    >> .


    --
     
    BruceM via AccessMonster.com, Jan 26, 2010
    #9
  10. oldblindpew

    oldblindpew Guest

    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]
    > >>
    > >> .

    >
    > --

    >
    > .
    >
     
    oldblindpew, Jan 26, 2010
    #10
  11. oldblindpew

    Steve Guest

    It is not clear to me if Agreements, Certs and Policies are standardized in
    and of themselves. Presumably they are. It appears that an Agreement can
    have one or more Certs and a Cert can have one or more Policies. It appears
    that a Cert is (established ??) by someone identified by ProducerID and it
    appears that a Policy is provided by someone identified by ProviderID.
    Finally it appears that an Agreement is executed with someone identified by
    InsuredID. If all the above is true, consider this table structure:
    TblProducerID
    ProducerID
    Producer fields ...

    TblProvider
    ProviderID
    Provider fields ...

    TblInsured
    InsuredID
    Insured fields ....

    TblAgreement
    AgreementID
    Agreement fields ...

    TblCert
    CertID
    Cert fields ...

    TblPolicy
    PolicyID
    Policy fields ...

    TblCertPolicy
    CertPolicyID
    CertID
    PolicyID

    TblAgreementCertPolicy
    AgreementCertPolicyID
    AgreementID
    CertPolicyID

    TblAgreementCertPolicyToInsured
    AgreementCertPolicyToInsured
    AgreementCertPolicyID
    InsuredID
    IssueDate
    etc

    This table structure gives you a record of a specific Agreement containing a
    specific set of certs where each Cert contains a specific set of policies
    issued to a specific Insured identified by InsuredID.

    Steve







    "oldblindpew" <> wrote in message
    news:...
    > 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
    >
     
    Steve, Jan 27, 2010
    #11
  12. oldblindpew

    oldblindpew Guest

    Thanks for your reply, Steve.

    All of your perceptions are correct.

    I assume by "standardized" you mean that each of these entities has a
    standard set of fields. Everything is pretty well standardized until you get
    down to the level of Coverage Details, which I had not mentioned until my
    previous reply. At this level, coverage details differ by policy type, and
    are more subject to change over time. The Normalized approach would be to
    make a master list (table) of CoverageDetails, (or CoverageItems?), with a
    many-to-many relationship between Policies and CoverageDetails.

    It is more usual to say a Cert is "issued" (vs. "established") by a Producer.

    Your solution is more like what I expected to receive from the outset: some
    sort of multiplicity of join tables.

    I have asked before about the wisdom of combining or splitting Firms by
    type. Presently, all Firms are in a single table, with several Type fields
    to indicate what types of work the firm does. This seems to be the preferred
    approach. In your model, is there any reason why Insureds, Producers, and
    Providers couldn't all be in the same table of Firms?

    BTW, does anyone know why it is that if I search this forum for OldBlindPew,
    I only get some of my threads?

    Thanks again,
    OBP

    "Steve" wrote:

    > It is not clear to me if Agreements, Certs and Policies are standardized in
    > and of themselves. Presumably they are. It appears that an Agreement can
    > have one or more Certs and a Cert can have one or more Policies. It appears
    > that a Cert is (established ??) by someone identified by ProducerID and it
    > appears that a Policy is provided by someone identified by ProviderID.
    > Finally it appears that an Agreement is executed with someone identified by
    > InsuredID. If all the above is true, consider this table structure:
    > TblProducerID
    > ProducerID
    > Producer fields ...
    >
    > TblProvider
    > ProviderID
    > Provider fields ...
    >
    > TblInsured
    > InsuredID
    > Insured fields ....
    >
    > TblAgreement
    > AgreementID
    > Agreement fields ...
    >
    > TblCert
    > CertID
    > Cert fields ...
    >
    > TblPolicy
    > PolicyID
    > Policy fields ...
    >
    > TblCertPolicy
    > CertPolicyID
    > CertID
    > PolicyID
    >
    > TblAgreementCertPolicy
    > AgreementCertPolicyID
    > AgreementID
    > CertPolicyID
    >
    > TblAgreementCertPolicyToInsured
    > AgreementCertPolicyToInsured
    > AgreementCertPolicyID
    > InsuredID
    > IssueDate
    > etc
    >
    > This table structure gives you a record of a specific Agreement containing a
    > specific set of certs where each Cert contains a specific set of policies
    > issued to a specific Insured identified by InsuredID.
    >
    > Steve
    >
    >
    >
    >
    >
    >
    >
    > "oldblindpew" <> wrote in message
    > news:...
    > > 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
    > >

    >
    >
    > .
    >
     
    oldblindpew, Jan 27, 2010
    #12
  13. oldblindpew

    Steve Guest

    <<The Normalized approach would be to make a master list (table) of
    CoverageDetails, (or CoverageItems?), with a many-to-many relationship
    between Policies and CoverageDetails.>>
    Yes! Your tables would look like:
    TblPolicy
    PolicyID
    Policy fields ...

    TblCoverageDetail
    CoverageDetailID
    CoverageDetail fields ....

    TblPolicyCoverageDetail
    PolicyCoverageDetailID
    PolicyID
    CoverageDetailID

    When coverage details of a policy change, you need to add the new details to
    TblCoverageDetail. This changes a policy so you also need to add a new
    record(s) to
    TblPolicyCoverageDetail.

    Using the tables I previously suggested, you can get the coverage details of
    an agreement in a query that includes TblPolicyCoverageDetail.


    <<In your model, is there any reason why Insureds, Producers, and Providers
    couldn't all be in the same table of Firms?>>

    If ALL (not most!!!) are firms with the same firm fields; yes, you can
    combine them into a TblFirm.

    Steve




    "oldblindpew" <> wrote in message
    news:...
    > Thanks for your reply, Steve.
    >
    > All of your perceptions are correct.
    >
    > I assume by "standardized" you mean that each of these entities has a
    > standard set of fields. Everything is pretty well standardized until you
    > get
    > down to the level of Coverage Details, which I had not mentioned until my
    > previous reply. At this level, coverage details differ by policy type,
    > and
    > are more subject to change over time. The Normalized approach would be to
    > make a master list (table) of CoverageDetails, (or CoverageItems?), with a
    > many-to-many relationship between Policies and CoverageDetails.
    >
    > It is more usual to say a Cert is "issued" (vs. "established") by a
    > Producer.
    >
    > Your solution is more like what I expected to receive from the outset:
    > some
    > sort of multiplicity of join tables.
    >
    > I have asked before about the wisdom of combining or splitting Firms by
    > type. Presently, all Firms are in a single table, with several Type
    > fields
    > to indicate what types of work the firm does. This seems to be the
    > preferred
    > approach. In your model, is there any reason why Insureds, Producers, and
    > Providers couldn't all be in the same table of Firms?
    >
    > BTW, does anyone know why it is that if I search this forum for
    > OldBlindPew,
    > I only get some of my threads?
    >
    > Thanks again,
    > OBP
    >
    > "Steve" wrote:
    >
    >> It is not clear to me if Agreements, Certs and Policies are standardized
    >> in
    >> and of themselves. Presumably they are. It appears that an Agreement can
    >> have one or more Certs and a Cert can have one or more Policies. It
    >> appears
    >> that a Cert is (established ??) by someone identified by ProducerID and
    >> it
    >> appears that a Policy is provided by someone identified by ProviderID.
    >> Finally it appears that an Agreement is executed with someone identified
    >> by
    >> InsuredID. If all the above is true, consider this table structure:
    >> TblProducerID
    >> ProducerID
    >> Producer fields ...
    >>
    >> TblProvider
    >> ProviderID
    >> Provider fields ...
    >>
    >> TblInsured
    >> InsuredID
    >> Insured fields ....
    >>
    >> TblAgreement
    >> AgreementID
    >> Agreement fields ...
    >>
    >> TblCert
    >> CertID
    >> Cert fields ...
    >>
    >> TblPolicy
    >> PolicyID
    >> Policy fields ...
    >>
    >> TblCertPolicy
    >> CertPolicyID
    >> CertID
    >> PolicyID
    >>
    >> TblAgreementCertPolicy
    >> AgreementCertPolicyID
    >> AgreementID
    >> CertPolicyID
    >>
    >> TblAgreementCertPolicyToInsured
    >> AgreementCertPolicyToInsured
    >> AgreementCertPolicyID
    >> InsuredID
    >> IssueDate
    >> etc
    >>
    >> This table structure gives you a record of a specific Agreement
    >> containing a
    >> specific set of certs where each Cert contains a specific set of policies
    >> issued to a specific Insured identified by InsuredID.
    >>
    >> Steve
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >> "oldblindpew" <> wrote in message
    >> news:...
    >> > 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
    >> >

    >>
    >>
    >> .
    >>
     
    Steve, Jan 27, 2010
    #13
  14. I will reply in this part of the thread, as it is the most recent. It's
    somewhat difficult to say whether firms should be in one table or split into
    several. As an example, Customers and Vendors are both companies with
    addresses, etc., but are likely to contain substantially different types of
    data, so it makes sense in most cases that they be in the same table. On the
    other hand, supply vendors and service vendors are both vendors, even though
    you may need certain fields for one and not the other (shipping information
    may not apply to a service vendor, for instance). I part company here with
    Steve's insistence that *all* of the fields need to be the same, but I think
    it is true they should be substantially similar.

    I do not really understand the concepts of Producer, Provider, and Insured as
    they apply to your situation. If the Insured is the customer and the other
    two are suppliers, the tables should be split up to that extent, I would
    think. In any case, if you are using a combo or list box you probably want
    to limit the list only to those who could by Providers or Producers or
    Insureds.

    Regarding the forums search, if you are using the Microsoft interface it can
    be clunky. A Google Groups search is more likely to return the information.

    oldblindpew wrote:
    >Thanks for your reply, Steve.
    >
    >All of your perceptions are correct.
    >
    >I assume by "standardized" you mean that each of these entities has a
    >standard set of fields. Everything is pretty well standardized until you get
    >down to the level of Coverage Details, which I had not mentioned until my
    >previous reply. At this level, coverage details differ by policy type, and
    >are more subject to change over time. The Normalized approach would be to
    >make a master list (table) of CoverageDetails, (or CoverageItems?), with a
    >many-to-many relationship between Policies and CoverageDetails.
    >
    >It is more usual to say a Cert is "issued" (vs. "established") by a Producer.
    >
    >Your solution is more like what I expected to receive from the outset: some
    >sort of multiplicity of join tables.
    >
    >I have asked before about the wisdom of combining or splitting Firms by
    >type. Presently, all Firms are in a single table, with several Type fields
    >to indicate what types of work the firm does. This seems to be the preferred
    >approach. In your model, is there any reason why Insureds, Producers, and
    >Providers couldn't all be in the same table of Firms?
    >
    >BTW, does anyone know why it is that if I search this forum for OldBlindPew,
    >I only get some of my threads?
    >
    >Thanks again,
    >OBP
    >
    >> It is not clear to me if Agreements, Certs and Policies are standardized in
    >> and of themselves. Presumably they are. It appears that an Agreement can

    >[quoted text clipped - 113 lines]
    >>
    >> .


    --

     
    BruceM via AccessMonster.com, Jan 28, 2010
    #14
  15. oldblindpew

    andy t Guest

    Quoting oldblindpew in microsoft.public.access.tablesdbdesign:

    >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


    [Sorry for this late post, but I'm reading up on the group...]

    Is CERTSPOLICIES a weak entity to any other entity in your model? That is,
    exists any other table (not shown here) that needs an FK to CERTSPOLICIES?
    If not, what is the reason for using a surrogate key there (in
    CERTSPOLICIES)? Wouldn't one always join and search that table using CertID
    and/or PolicyID, but never CertsPoliciesID (which then is totally
    redundant, simply taking up space and doing nothing useful)?
     
    andy t, Jan 3, 2012
    #15
  16. oldblindpew

    andy t Guest

    Quoting andy t in microsoft.public.access.tablesdbdesign:
    >Quoting oldblindpew in microsoft.public.access.tablesdbdesign:
    >
    >>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

    >
    >[Sorry for this late post, but I'm reading up on the group...]
    >
    >Is CERTSPOLICIES a weak entity to any other entity in your model? That is,

    ^^^^
    That should, of course, read "strong", not weak...

    >exists any other table (not shown here) that needs an FK to CERTSPOLICIES?
    >If not, what is the reason for using a surrogate key there (in
    >CERTSPOLICIES)? Wouldn't one always join and search that table using CertID
    >and/or PolicyID, but never CertsPoliciesID (which then is totally
    >redundant, simply taking up space and doing nothing useful)?
     
    andy t, Jan 3, 2012
    #16
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Josh

    Avoiding column limit in Access table

    Josh, Feb 18, 2004, in forum: Access Table Design
    Replies:
    1
    Views:
    86
    Tim Ferguson
    Feb 18, 2004
  2. hermie

    avoiding double entry in table

    hermie, Aug 26, 2004, in forum: Access Table Design
    Replies:
    2
    Views:
    127
    Armen Stein
    Aug 27, 2004
  3. Bruce Rusk

    Indexes on multiple fields -- redundant to reindex?

    Bruce Rusk, Jul 6, 2005, in forum: Access Table Design
    Replies:
    5
    Views:
    113
    Bruce Rusk
    Jul 7, 2005
  4. dawnecia

    Avoiding duplicate records - Acc97

    dawnecia, Sep 7, 2005, in forum: Access Table Design
    Replies:
    5
    Views:
    100
  5. neil
    Replies:
    2
    Views:
    123
    J. Goddard
    Oct 10, 2006
  6. mscertified
    Replies:
    2
    Views:
    94
    David W. Fenton
    Aug 23, 2007
  7. Jacqueline

    redundant data - help with Contact Database

    Jacqueline, Dec 19, 2008, in forum: Access Table Design
    Replies:
    6
    Views:
    154
    Jeff Boyce
    Dec 22, 2008
  8. Rand605

    Avoid redundant table design

    Rand605, Dec 3, 2009, in forum: Access Table Design
    Replies:
    1
    Views:
    145
    Gina Whipp
    Dec 3, 2009
Loading...