I have table named 'Dictionary' with columns as follow:
我有一个名为'Dictionary'的表,其列如下:
- ID bigint
- TYPE varchar (200)
- ITEM varchar (200)
TYPE varchar(200)
ITEM varchar(200)
Table is used by various tables as simple dictionary / lookup.
Eg it stores countries, titles, business type lists.
表被各种表用作简单的字典/查找。例如,它存储国家,标题,业务类型列表。
TYPE column keeps info about type of dictionary , ITEM is dictionary string value.
TYPE列保存有关字典类型的信息,ITEM是字典字符串值。
All works well but I have problem to set up relationship between dictionary and foreigin tables.
When I'm using 'Foreign Key Relationship' I can not make it depended of 'TYPE" column.
一切正常但我在设置字典和foreigin表之间的关系时遇到了问题。当我使用“外键关系”时,我无法将其视为“TYPE”列。
(Please note same item with same type - eg 'countries' can be linked to several tables, when item with another type can be linked to different)
(请注意相同类型的相同项目 - 例如,'countries'可以链接到多个表,当具有其他类型的项目可以链接到不同时)
Currently I'm using USPs to manage that but I'd like to switch to standard relationship mechanism.
Any advice how to get that?
目前我正在使用USP来管理它,但我想切换到标准关系机制。任何建议如何获得?
3 个解决方案
#1
It looks to me that you could consider an alternative design
在我看来,你可以考虑另一种设计
Dictionary table
ID (pk)
DICTIONARY_TYPE_ID (fk to dictionaryType)
ITEM
DictionaryType table
ID (pk)
DESCRIPTION
and then make links to the ID of DictionaryType table in places where you currently want to reference Type field from your original design
然后在您当前要从原始设计中引用“类型”字段的位置链接到DictionaryType表的ID
#2
From the context of the question, I'm guessing you'll need to do one of two things:
从问题的背景来看,我猜你需要做两件事之一:
- Make your
Typecolumn the primary key - or have the foreign keys depend on the
IDfield here.
将“类型”列设为主键
或者外键取决于ID字段。
Foreign keys need to refer to a primary key, and it looks like your Type column isn't a PK.
外键需要引用主键,看起来你的Type列不是PK。
#3
what you have here is an EAV db design which is bad for number of reasons one being your problem. there is no solution for this in the real sense. you might try using sql_variant as a column type for the item and try to to a PK-FK relationship on that.
你在这里有一个EAV数据库设计,这是由于一些你的问题的原因很糟糕。真正意义上没有解决方案。您可以尝试使用sql_variant作为项的列类型,并尝试在其上使用PK-FK关系。
there's another way you could try to do this with the xml datatype and schemas like i describe here. however you'll have to test this to see if it applies to your problem.
还有另一种方法可以尝试使用xml数据类型和我在此描述的模式。但是你必须测试它,看看它是否适用于你的问题。
#1
It looks to me that you could consider an alternative design
在我看来,你可以考虑另一种设计
Dictionary table
ID (pk)
DICTIONARY_TYPE_ID (fk to dictionaryType)
ITEM
DictionaryType table
ID (pk)
DESCRIPTION
and then make links to the ID of DictionaryType table in places where you currently want to reference Type field from your original design
然后在您当前要从原始设计中引用“类型”字段的位置链接到DictionaryType表的ID
#2
From the context of the question, I'm guessing you'll need to do one of two things:
从问题的背景来看,我猜你需要做两件事之一:
- Make your
Typecolumn the primary key - or have the foreign keys depend on the
IDfield here.
将“类型”列设为主键
或者外键取决于ID字段。
Foreign keys need to refer to a primary key, and it looks like your Type column isn't a PK.
外键需要引用主键,看起来你的Type列不是PK。
#3
what you have here is an EAV db design which is bad for number of reasons one being your problem. there is no solution for this in the real sense. you might try using sql_variant as a column type for the item and try to to a PK-FK relationship on that.
你在这里有一个EAV数据库设计,这是由于一些你的问题的原因很糟糕。真正意义上没有解决方案。您可以尝试使用sql_variant作为项的列类型,并尝试在其上使用PK-FK关系。
there's another way you could try to do this with the xml datatype and schemas like i describe here. however you'll have to test this to see if it applies to your problem.
还有另一种方法可以尝试使用xml数据类型和我在此描述的模式。但是你必须测试它,看看它是否适用于你的问题。