database structure:primary key debate

[jonamukundu]

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

[FishVal]

Indexes are good alternative to primary key constraints.

Though to meet your business rules (avoid duplicates within a certain time period) you'd better use VBA validation routine on input and/or on table data, unless you will design a separate table storing those time periods.

Kind regards,
Fish

[Stewart Ross]

Hi. Perhaps for simplification you have left out some details, but since there is no date listed in either of your facility 1 and 2 choices these do not reflect the 'multiple collections within a time period' part of your post.

The clinic ID is a foreign key carried forward from your first table, and the indicator ID from your indicator table. You will need these regardless of introducing an artificial primary key on your facility 1 or 2 table.

In some circumstances the use of a composite key limits what you can do (for example, it is trickier to work with multiple key components if you want to use combo boxes to select particular records on a form than to use single-component keys) - but in my opinion you should use what is most likely to remain correct given future expansion and development of your tables. In this case, if the candidate key for your table is a composite one then use it.

Autonumbers are often used as primary keys, but as they can be added to tables in ways which bear no relation to the contents or uses of the tables I do not myself support using them just to avoid using a composite key. It is inevitable that in any application of any size composite keys will arise (for example every link table used to decompose many-many relationships has a composite key). Using an autonumber key in such circumstances would make no sense, unless there is an advantage to be gained that outweighs the apparent breach of normalisation involved.

There is a considerable difference between data validation (ensuring that the values entered are correct in range, value and type for your application, and that the values satisfy any business rules defined) and table normalisation (ensuring that the tables are optimally structured to minimise data redundancy and to eradicate update anomalies).

In the example you mention, two collections for a clinic within a certain period will not violate any key constraints at all as long as the collection dates are different, no matter how you choose the primary key of that table. Implementing a business rule like "no two collections within X period" involves decisions which restrict what would otherwise be valid data, and this has little or nothing to do with the value or nature of the primary key.

As FishVal points out, this is a data validation matter usually dealt with (in Access) through bespoke VBA coding.

-Stewart

[youmike]

I'd go one step further than Stewart and recommend that you use VBA rather than a macro. VBA is generally more robust, easier to debug and more flexible if you want to build your own error handling in.

Whilst totalling tables often appear to violate normalisation rules, they can speed the process of giving the user what he/she wants dramatically. Users mostly want to see summarised information, not raw data. I tend to keep such routines in stand-alone databases and copy the code to where it's needed, then adjust as needed.

One final point: it is usually good practice to limit the use of such totalling tables to creation of summary reports and not try to have users update the information they contain, just because they may violate normalisation rules. Badly normalised tables are snakes that will bite you just when you don't want them to!