Avoiding duplicate fields in a table

[PVANS]

Good morning

Please can someone help me with this:

I have a table tblCommission which has the following fields:
Commission_ID (primary key)
Client_account (related to the field Client_account in tblClients)
Product_Code (related to the field Product_Code in tblProducts)
Commission
Exchange

I need a method to ensure that each client can only have one commission per
product.... all clients should (and do) have the ability to have a commission
for all the possible products, but I need to protect my database from the
scenario I have shown below:

Commission_ID Client_account Product_code Commission Exchange
1 000001 Coke 2
$
2 000001 Coke 1
$

Please could someone suggest a method to protect the table from this
scenario occuring?

Thank you for the help, I really appreciate it.

 

[Piet Linden]

Good morning

Please can someone help me with this:

I have a table tblCommission which has the following fields:
Commission_ID (primary key)
Client_account (related to the field Client_account in tblClients)
Product_Code (related to the field Product_Code in tblProducts)
Commission
Exchange

I need a method to ensure that each client can only have one commission per
product.... all clients should (and do) have the ability to have a commission
for all the possible products, but I need to protect my database from the
scenario I have shown below:

Commission_ID   Client_account  Product_code   Commission   Exchange
1                                 000001              Coke                 2
                 $
2                                 000001              Coke                 1
                 $

Please could someone suggest a method to protect the table from this
scenario occuring?

Thank you for the help, I really appreciate it.

Add a unique index to (Client_account, Product_code) and you will not
be able to enter duplicates *ever*, so if this is a "sometimes" rule
instead of an "always" rule, you'll have to rethink your strategy.