Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, if I have say a customer who may or have a number of finite risks, lets say A B C D, and can have any combination or none of these. Are these simply part of the one side and part of the client in the same way a tax number may be or indeed the address details? Or are they a limited many relation? What is the best way of modelling, in my case there are possibly 14 risks. Would it be more appropriate to have the risks on a many side and users then select them say from a drop down.


    Thanks, Darren

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This is a many-to-many relationship: one customer can have several risks, and several customers can have the same risk.
    So you need a junction table with at least two fields: the customer ID and the risk ID; the combination of these fields could be the primary key of the junction table.
    The "standard" way to enter data is through a main form based on the customer table with a subform based on the junction table.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='776335' date='22-May-2009 08:17']This is a many-to-many relationship: one customer can have several risks, and several customers can have the same risk.
    So you need a junction table with at least two fields: the customer ID and the risk ID; the combination of these fields could be the primary key of the junction table.
    The "standard" way to enter data is through a main form based on the customer table with a subform based on the junction table.[/quote]
    Hi Hans, would you clarify this for me.
    I would have thought all he required was 3 tables, Risks, Customers and CustomerRisks, viz:

    Risks (lookup table)
    RiskID
    RiskDescription
    .
    .
    Customers
    CustID
    .
    .
    CustomerRisks
    CustRiskID (PK)
    CustID
    RiskID
    .
    Am i on the right tram or is this the wrong approach?

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='patt' post='776374' date='22-May-2009 02:17']Hi Hans, would you clarify this for me.
    I would have thought all he required was 3 tables, Risks, Customers and CustomerRisks, viz:

    Risks (lookup table)
    RiskID
    RiskDescription
    .
    .
    Customers
    CustID
    .
    .
    CustomerRisks
    CustRiskID (PK)
    CustID
    RiskID
    .
    Am i on the right tram or is this the wrong approach?[/quote]

    Looks good to me - if the combination of Customer and Risk must be unique (i.e. a Customer can only ever have a type of Risk once) then you could use a composite Primary Key for the CustomerRisks table. If there is another factor (e.g. a Customer could have the same Risk at different times, you would need to include a date field within the table and possibly within the composite key (though 3-way composite keys are rather cumbersome to work with)
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Pat,

    That corresponds to what I suggested, except that I would make the combination of CustID and RiskID the primary key of the table (unless you need to add another field to make the combinations unique, as mentioned by SteveH).

    See this post for an example of how to handle data entry for a many-to-many relationship with a main form and subform.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='SteveH' post='776391' date='22-May-2009 17:45']Looks good to me - if the combination of Customer and Risk must be unique (i.e. a Customer can only ever have a type of Risk once) then you could use a composite Primary Key for the CustomerRisks table. If there is another factor (e.g. a Customer could have the same Risk at different times, you would need to include a date field within the table and possibly within the composite key (though 3-way composite keys are rather cumbersome to work with)[/quote]
    That was my thinking Steve, although you dont need a date field, you can just use the CustRiskID field as the 3rd field in the key, after all it's a autonumber field, so that is date/time field order anyway.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='patt' post='776400' date='22-May-2009 11:17']That was my thinking Steve, although you dont need a date field, you can just use the CustRiskID field as the 3rd field in the key, after all it's a autonumber field, so that is date/time field order anyway.[/quote]
    The idea of a composite primary key is to force the combinations of the field values to be unique. Of course, one could also create another unique index for this purpose, but using a composite primary key is clean and simple.

    For example, if a customer can have each risk only once, this would be allowed:
    [attachment=83899:x.png]
    But this wouldn't, because CustID 789 has the same RiskID twice:
    [attachment=83900:x2.png]
    Having an AutoNumber primary key CustRiskID wouldn't prohibit this, but having a composite primary key on the combination of CustID and RiskID would.

    The same reasoning holds if you add a date/time field into the mix: an AutoNumber primary key wouldn't ensure that the combinations of CustID, RiskID and date field would be unique.
    Attached Images Attached Images

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the replies folks. Most helpful.


    Cheers, Darren.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='776401' date='22-May-2009 20:34']The idea of a composite primary key is to force the combinations of the field values to be unique. Of course, one could also create another unique index for this purpose, but using a composite primary key is clean and simple.

    For example, if a customer can have each risk only once, this would be allowed:
    [attachment=83899:x.png]
    But this wouldn't, because CustID 789 has the same RiskID twice:
    [attachment=83900:x2.png]
    Having an AutoNumber primary key CustRiskID wouldn't prohibit this, but having a composite primary key on the combination of CustID and RiskID would.

    The same reasoning holds if you add a date/time field into the mix: an AutoNumber primary key wouldn't ensure that the combinations of CustID, RiskID and date field would be unique.[/quote]
    I didn't explain myself enough, what i alluded to was to include the CustRiskID field alongwith the other 2 fields to formthe primary key if there were duplicate risks allowed.
    So the prmary key of that table would be:
    CustID
    RiskID
    CustRiskID

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='patt' post='776518' date='23-May-2009 10:43']I didn't explain myself enough, what i alluded to was to include the CustRiskID field alongwith the other 2 fields to formthe primary key if there were duplicate risks allowed.
    So the prmary key of that table would be:
    CustID
    RiskID
    CustRiskID[/quote]
    If you have an autonumber field CustRiskID I can't see any reason not to make it the sole key. What do you gain by adding the other fields to make a composite key?
    I nearly always use an autonumber as the primary key as it simplifies the writing of Where clauses to identify the record, and if you need another table where the key of the junction table is to be a foreign key, a single field will do the job.

    I actually don't like the term Junction table. Talking about Junction tables mystifies them. Junction tables correspond to an entity like any other table.
    Regards
    John



  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='johnhutchison' post='776546' date='23-May-2009 13:15']If you have an autonumber field CustRiskID I can't see any reason not to make it the sole key. What do you gain by adding the other fields to make a composite key?
    I nearly always use an autonumber as the primary key as it simplifies the writing of Where clauses to identify the record, and if you need another table where the key of the junction table is to be a foreign key, a single field will do the job.

    I actually don't like the term Junction table. Talking about Junction tables mystifies them. Junction tables correspond to an entity like any other table.[/quote]
    The point of including CustRiskID as part of a 3 way key is required if you allow duplicate risks for the same customer and providing a unique key, mind you if you turn off the uniqueness of CustID and RiskID when allowing duplicates then you have the same effect, so in effect you dont need an extra field called CustRiskID. All you need do is to either make the CustID and RiskID unique if only one risk is allowed per customer aor dont make it unique if there are duplicates allowed.

    All very complex this stuff eh.

    I agree re your argument for the PK being an autonumber.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='patt' post='776778' date='25-May-2009 14:34']The point of including CustRiskID as part of a 3 way key is required if you allow duplicate risks for the same customer and providing a unique key, mind you if you turn off the uniqueness of CustID and RiskID when allowing duplicates then you have the same effect, so in effect you dont need an extra field called CustRiskID. All you need do is to either make the CustID and RiskID unique if only one risk is allowed per customer aor dont make it unique if there are duplicates allowed.[/quote]
    Once you add CustRiskID to the key, you might as well remove CustID and RiskID as CustRiskID uniquely identifies a record by itself. If you want to prevent duplicates then create a unique index on the two other fields, that is separate from the key.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •