Composite Key on a Self Referencing Table

Question

We have a composite primary key for the site table defined below. Functionally, this does exactly as we would like it to. Each site should have a parent site of the same district. Defining the table in this way allows specifically for that.

 CREATE TABLE [dbo].[site](
  [site_number] [nvarchar](50) NOT NULL,
  [district_id] [bigint] NOT NULL,
  [partner_site_number] [nvarchar](50) NULL,
  CONSTRAINT [PK_site] PRIMARY KEY CLUSTERED 
 (
  [site_number] ASC,
  [district_id] ASC
 )

 ALTER TABLE [dbo].[site]  WITH CHECK ADD  CONSTRAINT [FK_site_site] FOREIGN KEY([partner_site_number], [district_id])

My specific question is regarding the self-referencing FK defined on a composite PK. I've heard a few opinions on this particular design and they tend to be conflicting. Some like it particularly because it functions as it should within a general understanding of composite keys. Others insist that it is theoretically incorrect and that there should also be a [partner_district_id] field that is included in the FK instead of [district_id]. This design would require validation to enforce that the [district_id] = [partner_district_id], which could be done either with a check constraint or application level logic.

Further opinions on these solutions or any others would be appreciated.

Answer 1

I'd suggest SiteId on it's own be the primary key. DistrictId should probably be a foreign key?

EDIT - in that case, i'd suggest adding the additional PartnerDistrictId to the foreign key; you never know, you may later want to partner one site with another in a different district. But personally, I'd be in favour of a surrogate key here. And in most cases ;)

Answer 2

naming comment... Is the Site_Id by itself not unique? Cause the name Site_id implies that iut is. If it is only unique in combination with District_Id, then it is perhaps misnamed... it might be clearer if it were site_Sequence, or District_site_No, or something else clearer.

If I understand your domain model, then all sites in a district 'derive' from the same root parent site, and there can be no overlap between sitres in different districts... if so then the same functionality could be achioeved by making DistrictID nullable, and only populate it for the root sites. Then the Site_Id could be a single field PK, and ParentSiteId could be a single Field FK. All 'child' sites would 'belong' to the district designated in their root parent Site record.