Dictionary table relationships (MS SQL 2005)

Question

I have table named 'Dictionary' with columns as follow:

• ID bigint
• 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.

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.

(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)

Currently I'm using USPs to manage that but I'd like to switch to standard relationship mechanism.
Any advice how to get that?

Answer 1

From the context of the question, I'm guessing you'll need to do one of two things:

• Make your Type column the primary key
• or have the foreign keys depend on the ID field here.

Foreign keys need to refer to a primary key, and it looks like your Type column isn't a PK.

Answer 2

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.

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.

Answer 3

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