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 these 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
FacilityData2: has an auto generated primary key named DataID
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.
see attachment...
any comments????
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 these 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
FacilityData2: has an auto generated primary key named DataID
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.
see attachment...
any comments????