hi all
I have a question on database design concerning the hot topic of primary keys.
Here is the scenario:

I have two lookup tables:
TABLE:tbl_Clinic------------------------these are clinic
Primary Key
CliniID ClinicName
1 Clinic 1
2 Clinic 2


TABLE:tbl_Indicators-----indicators recorded by each clinic
Primary Key
Indicator_ID IndicatorName
1 IND 1
2 IND 2
3 IND 3

i then have thse two options to collect indicator data for
each clinic. A clinic can only collect one indicator in a
specified period say a month.


Below are my two options: FacilityData1 and FacilityData2
FacilityData1:has a composite primary key of CliniID and Indicator_ID

FacilityData1
Primary Key
CliniID Indicator_ID Number
1 IND 1 1
1 IND 2 2
1 IND 3 6
2 IND 1 10
2 IND 2 5
2 IND 3 8


FacilityData2: has an auto generated primary key named DataID


FacilityData2
DataID CliniID Indicator_ID Number
1 1 IND 1 1
2 1 IND 2 2
3 1 IND 3 6
4 2 IND 1 10
5 2 IND 2 5
6 2 IND 3 8



Business Rule: i want to avoid duplicate entries where say
Clinic 1 collects IND1 twice in a particular period say a month.

Which of the options is best.
My opinion:
FacilityData1 avoids duplicates but introduces a composites keys which is discouraged
in many books and articles i have read.

FacilityData2 uses a single unique primary key but can introduce duplicates.

any comments????