1

(I'm using SQL Server 2008 R2 but I'm not sure that's important)

If I have the following "base" tables:

SettingTypeCategory  
- SettingTypeCategoryId (PK)  
- CategoryName

SettingType  
- SettingTypeId (PK)  
- SettingTypeCategoryId (FK)  
- SettingName

And I want to create multiple "custom" settings and categories like so:

CustomSettingTypeCategory
- CustomSettingTypeCategoryId (PK)
- SettingTypeCategoryId (FK)
- CustomCategoryName

CustomSettingType  
- CustomSettingTypeId (PK)  
- CustomSettingTypeCategoryId (FK)
- SettingTypeId (FK)  
- CustomSettingValue

EDIT:

What's the best way to ensure that all CustomSettingTypes with the same CustomSettingTypeCategoryId are of the same SettingTypeCategoryId (FK to SettingType.SettingTypeCategoryId) as the CustomSettingTypeCategory.SettingTypeCategoryId?

I could add a SettingTypeCategoryId to the CustomSettingType table and have a combined FK to the CustomSettingTypeCategory table but this approach makes me cringe a little because it's redundant, you know what Category a CustomSetting belongs to through it's FK relationship to SettingType, but other than using a trigger I don't see any other way around it.

Rob
  • 10,004
  • 5
  • 61
  • 91
  • If a custom setting type can only belong to one custom settting type category, which in turn can only belong to one setting type, then surely your `FK` in `CustomSettingTypeCategory` should be to `SettingTypeId`, not `SettingTypeCategoryId`. This is ofcourse unless a custom setting type can be assigned to a `SettingTypeId` with a different `SettingTypeCategoryId` to it's related `CustomSettingTypeCategoryId`? – GarethD Jun 13 '13 at 11:16
  • CustomSettingTypeCategory has a FK to SettingTypeCategory there is no FK to SettingType. Categories are Setting agnostic. Creating your suggested FK would result in a strange many to one - one to one relationship between categories and settings – Rob Jun 13 '13 at 11:20
  • Ok, just re-read the question, and still don't get it. Your main question appears to be *What's the best way to ensure that all CustomSettingTypes in the same CustomSettingTypeCategory are of the same SettingTypeCategory?*, yet your current table structure ensures this. Since `SettingTypeCategory` is set in `CustomSettingTypeCategory`, by definition all `CustomSettingType`s of the same `CustomSettingTypeCategory` have the same `SettingTypeFCategory`. If I'm missing something can you elaborate the question please? – GarethD Jun 13 '13 at 11:29
  • No it doesn't - there is nothing to stop 2 CustomSettingTypes that have two different CategoryIds (derived from FK settingid relationship) being in the same CustomSettingTypeCategory which has a specific CategoryId = I want to ensure that if I have a CustomCategory of say CategoryId 2 that all CustomSettingValues have FKs to SettingTypes with a CategoryId of 2 – Rob Jun 13 '13 at 11:33

0 Answers0