database structure:primary key debate

[mukudu99]

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????

 

[stopher]

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

I learnt relational database theory before doing any practical stuff. Composite keys are a core part of that theory. However, I have seen people comment that have some problems in practice (although I can't remember what they are).

In practice I often used composite keys and never have a problem. It does the job it's supposed to. So in this instance I'd use solution 1.

Chris

 

[namliam]

However, you say you dont want duplicate values in a certain period, thus allowing duplicates outside of that period....

How come your model dont have a date field??
Also if you want to use a PK to enforce this rule... You would have to add this period field to the PK...

On the other hand.... PK should be PK, not validation rules. Using a composit key has its usefullness but can be tricky at times, because in a 'proper' design all the fields in the table/record should depend on the FULL key, not part of the key.
And it seems to me like you are allready having an issue sticking to this, not including your period.

Business rules have to be enforced by DB logic, not (necesarily) by PKs

 

[stopher]

How come your model dont have a date field??
Also if you want to use a PK to enforce this rule... You would have to add this period field to the PK...

I assumed the IND was the period. A big assumption I admit!

 

[mukudu99]

Thanks to all
I did not include the date field for simplicity...
I just wanted to get your opinions on the choice between natural keys and surrogate keys.
I have spent the whole day researching on this topic and my conclusion is as follows:
It makes design good practice to have a meaningless surrogate key for any table in the database. This could ideally be an auto generated one like the one generated by access.
Duplicates can be handled by setting constraints on the database and programming the User Interface to detect and give a friendly message to a user in case a duplicate is attempted.
There are however certain situations where composite keys can come in handy and a friendly error message can still be generated when a duplicate is attempted..
I found this debate interesting:
http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx
Looks like 1+1 is not always equal to 2 in Database design

the pleasure is mine

thanks

 

[namliam]

Looks like 1+1 is not always equal to 2 in Database design

And there is no black and white, no right and wrong...
There are a lot of grey areas where you will go left and I will go right without anyone choice beeing the lesser one.

 

[neileg]

Don't forget that you can use a composite index set to no duplicates and have a separate autonumber PK.

 

[stopher]

Thanks to all
I did not include the date field for simplicity...
I just wanted to get your opinions on the choice between natural keys and surrogate keys.
I have spent the whole day researching on this topic and my conclusion is as follows:....

Hmmm. You've provided a link that contradicts your conclusions. Perhaps you could provide the links that support your conclusions.

It makes design good practice to have a meaningless surrogate key for any table in the database. This could ideally be an auto generated one like the one generated by access.

No. There are times where a surrogate key is appropriate e.g. where you can't be 100% sure of the validity of the obvious physical PK. But there are times when it adds no value. What's the point of adding a surrogate key to a junction table where the composite PK is well defined (see your example link). So surrogate keys should only be used when appropriate.

Duplicates can be handled by setting constraints on the database and programming the User Interface to detect and give a friendly message to a user in case a duplicate is attempted.

This would rather negate the need for relational database theory. Duplicates are handled by the relational database design. To try and manage this at the user interface level is just re-inventing the wheel.

Regards
Chris