Can a field reference to 2 different FKs or any better option for this case?

I am stuck with this situation: I have my main system settings table to hold all the default system values. An example is default language is English. default filte开发者_Python百科r is to have age restriction on for filtering user content, etc. There are about 40-ish different values to maintain for now.

Now all these values are stored in multiple lookup tables and also in the master field_value table which has all the small lookups. Language codes are in the language lookup table. Age filter has its own lookup table, and many others. Other smaller settings are all in the field_value table.

So I need to reference the setting_value field to all these tables unless i have 1 colunm per setting and keep the rest as NULL.

Currently my schema is this

CREATE TABLE `settings` (
  `setting_id` int(6) NOT NULL,
  `description` varchar(32) NOT NULL,
  `code` int(6) DEFAULT NULL,
  `created` datetime NOT NULL,
  `updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_id`),
  KEY `code` (`code`),
  CONSTRAINT `settings_ibfk_1` FOREIGN KEY (`code`) REFERENCES `field_values` (`fv_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And it does not work to handle settings from multiple tables across the system.

You could try super-type/subtype. Keep all common columns in the Setting table.

create table Setting (
      SettingID   integer     not null
    , SettingType char(1)     not null
    , SettingName varchar(32) not null
    , Description varchar(32) not null
    , Created     timestamp default CURRENT_TIMESTAMP
    -- other common-to-all-setups columns here
);
alter table Setting add constraint pk_setting primary key (SettingID);


create table LanguageSetting (
      SettingID   integer     not null
    , Updated     timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    -- other columns specific to this setup-type here
);
alter table LanguageSetting 
  add constraint pk_langset  primary key (SettingID)
, add constraint fk1_langset foreign key (SettingID) references Setting(SettingID);


create table AgeFilterSetting (
      SettingID   integer     not null
    , Updated     timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    -- other columns specific to this setup-type here
);
alter table  AgeFilterSetting 
  add constraint pk_ageflt  primary key (SettingID)
, add constraint fk1_ageflt foreign key (SettingID) references Setting(SettingID);


create table OtherSetting (
      SettingID   integer     not null
    , Updated     timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    -- other columns specific to this setup-type here
);
alter table  OtherSetting 
  add constraint pk_othset  primary key (SettingID)
, add constraint fk1_othset foreign key (SettingID) references Setting(SettingID);

.

You might use inheretance in your data model. The parent table has a primary-key and the children table have their primary-key as a foreign-key to the parent table. Here is another SO question with a similar thread.

MSSQL DDL Example:

CREATE TABLE [Child1](
    [ParentId]  int    NOT NULL,
    CONSTRAINT [PK2] PRIMARY KEY CLUSTERED ([ParentId])
)

CREATE TABLE [Child2](
    [ParentId]  int    NOT NULL,
    CONSTRAINT [PK3] PRIMARY KEY CLUSTERED ([ParentId])
)

CREATE TABLE [Parent](
    [ParentId]  int    NOT NULL,
    CONSTRAINT [PK1] PRIMARY KEY CLUSTERED ([ParentId])
)


ALTER TABLE [Child1] ADD CONSTRAINT [RefParent1] 
    FOREIGN KEY ([ParentId])
    REFERENCES [Parent]([ParentId])

ALTER TABLE [Child2] ADD CONSTRAINT [RefParent3] 
    FOREIGN KEY ([ParentId])
    REFERENCES [Parent]([ParentId])