SQL constraint over multiple tables-CodePudding

I have a small group of tables that I'm connecting with SQL Server.

Here's a basic diagram:

Note: there are other columns in some tables which I've removed for brevity.

All of this works okay, but the design isn't as tight as it could/should be.

PageParams relate to a PageVariant which relates to a Page
RouteParams relate to a Route which relates to a Page

Here's the issue:

PageParam also connects to RouteParam, and that causes an issue with regard to integrity.

PageParam ultimately relates back to a Page. So does RouteParam.

However, with the current design, the Page could be different in each case - there is no constraint to ensure it's the same, and it needs to be. I'm aware that an extra table/columns may be required, but I'm struggling to see the logic of how to make this work.

Any advice appreciated.

CodePudding user response：

Add a PageID to RouteParam and make the FK (PageID, RouteID). Also propagate the PageID down to PageParam. PageParam would then have the two FKs: FK (PageID, PageVarianID), FK (PageID, RouteParamId).

Since the foreign keys must relate to the primary keys or to unique indexes, it will also be necessary to either create compound primary keys or to add compound unique indexes.

   PageParam                                              PageVariant
  ┌────────────────────────┐                             ┌───────────────────────┐
  │ PageParamID    PK      │    PageID, PageVariantID    │ PageVariantID   PK, UX│
  │ PageID         FK1, FK2├───────────────────────────► │ PageID          FK, UX│
  │ PageVariantID  FK1     │                             └─────────┬─────────────┘
  │ RouteParamID   FK2     │                                       │
  └─────────────┬──────────┘                                       │
                │                                                  │PageID
                │PageID, RouteParamID                              │
                │                                                  │
   RouteParam   ▼                Route                    Page     ▼
  ┌──────────────────────┐      ┌──────────────────┐     ┌──────────────┐
  │ RouteParamID  PK, UX │      │  RouteID  PK, UX │     │ PageID  PK   │
  │ PageID        FK, UX ├─────►│  PageID   FK, UX ├────►│              │
  │ RouteID       FK     │      └──────────────────┘     └──────────────┘
  └──────────────────────┘ PageID                    PageID
                           RouteID