Thanks Doug for being so specific.
I appreciate it. Big change from how they were doing it, but much better.

Sylvia



>Hi Sylvia.
>
>>My problem is three deep.
>>
>>1- facility Candidatekey- facilityno
>>2- wellunit Candidatekey- facilityno+unitno
>>3- wellinsp Candidatekey- facilityno+unitno+dtos(inspdate)
>
>This is how I'd do it:
>
>FACILITY: ID: primary key (assigned by system), FACILITYNO (if needed): candidate key
>
>WELLUNIT: ID: primary key (assigned by system), FACILITY_ID (foreign key linked to FACILITY.ID), FACILITYNO + UNITNO: candidate key
>
>WELLINSP: ID: primary key (assigned by system), WELLUNIT_ID (foreign key linked to WELLUNIT.ID), FACILITYNO + UNITNO + DTOS(INSPDATE): candidate key
>
>With this design, the relationships are defined on single fields. If possible, I'd even remove the FACILITYNO fields from WELLUNIT and WELLINSP and the UNITNO field from WELLINSP, since you'll have to cascade changes to those fields to child tables.
>
>Doug