create table #domains(domainname varchar(20) not null primary key, domaindefinition varchar(256) not null, domainvalues text null)

insert #domains
select 'sqltypes', '.....', '<domainroot><value>bigint</value><value>binary</value><value>bit</value><value>char</value><value>datetime</value><value>decimal</value></domainroot>'

select domainname, domainvalues from #domains where domainname = 'sqltypes'

drop table #domains

-- Each lookup list gets 1 entry here (the header table)
create table LookupLists (ListID int primary key,
                          ListDesc varchar(100))

-- All lookup values for each list are stored here (details)
create table LookupListValues (ListID int references LookupLists (ListID),
                               Code varchar(10) not null,
                               CodeDesc varchar(100) not null,
primary key (ListID, Code))

go

-- some sample data in our lookup tables:

insert into LookupLists (ListID, ListDesc)
select 1, 'Status Codes' union
select 2, 'Priority Codes'

insert into LookupListValues (ListID, Code, CodeDesc)
select 1,'A','Active' union
select 1,'I','Inactive' union

select 2,'H','High' union
select 2,'M','Medium' union
select 2,'L','Low'


-- we can use the UDF technique to write our CHECK constraint:
go
create function CheckLookup(@ListID int, @Code varchar(10))
returns bit
as
begin
  return case when exists (select * from LookupListValues where ListID= @ListID and @Code = Code)
      then 1 else 0 end
end

-- Now, we can use some tricks to allow for RI on columns in particular tables
-- to reference a particular Lookup list, like this:
go

create table StuffWithStatuses (ID int primary key,
                     Col1 varchar(10),
                     StatusCode varchar(10) check (dbo.CheckLookup(1,StatusCode)=1))

go
create table StuffWithPriorities (ID int primary key,
                     Col1 varchar(10),
                     PriorityCode varchar(10) check (dbo.CheckLookup(2,PriorityCode)=1))

go

-- INSERT OF STATUS CODE LOOKUP VALUES:              
-- will work ok:
insert into StuffWithStatuses (ID, Col1, StatusCode)
select 1,'Test1','A' union
select 2,'Test2','I'

-- will NOT work ok:
insert into StuffWithStatuses (ID, Col1, StatusCode)
select 3,'Test3','H' union
select 4,'Test4','X'

select * from StuffWithStatuses

--

-- INSERT OF PRIORTY CODE LOOKUP VALUES:              
-- will work ok:
insert into StuffWithPriorities (ID, Col1, PriorityCode)
select 1,'Test1','H' union
select 2,'Test2','M' union
select 3,'Test3','L'

-- will NOT work ok:
insert into StuffWithPriorities (ID, Col1, PriorityCode)
select 4,'Test4','A' union
select 5,'Test5','X'

select * from StuffWithPriorities

go
drop table StuffWithStatuses
drop table StuffWithPriorities
drop function CheckLookup
drop table LookupListValues
drop table LookupLists
go

-- Now, we can use some tricks to allow for RI on columns in particular tables
-- to reference a particular Lookup list, like this:
go

create table StuffWithStatuses (ID int primary key,
                     Col1 varchar(10),
                     StatusCode varchar(10),
                     Dummy int default (1) check (dummy=1),
constraint sws_fk foreign key (Dummy,StatusCode) references LookupListValues (ListID, Code)
)

go
create table StuffWithPriorities (ID int primary key,
                	Col1 varchar(10),
	                PriorityCode varchar(10),
   			Dummy int default (2) check (dummy=2),
constraint swp_fk foreign key (Dummy,PriorityCode) references LookupListValues (ListID, Code)
)

go

-- INSERT OF STATUS CODE LOOKUP VALUES:              
-- will work ok:
insert into StuffWithStatuses (ID, Col1, StatusCode)
select 1,'Test1','A' union
select 2,'Test2','I'

-- will NOT work ok:
insert into StuffWithStatuses (ID, Col1, StatusCode)
select 3,'Test3','H' union
select 4,'Test4','X'

select * from StuffWithStatuses

--

-- INSERT OF PRIORTY CODE LOOKUP VALUES:              
-- will work ok:
insert into StuffWithPriorities (ID, Col1, PriorityCode)
select 1,'Test1','H' union
select 2,'Test2','M' union
select 3,'Test3','L'

-- will NOT work ok:
insert into StuffWithPriorities (ID, Col1, PriorityCode)
select 4,'Test4','A' union
select 5,'Test5','X'

select * from StuffWithPriorities

-- and, now, this should fail:

delete from LookupListValues
where ListID = 2 and Code = 'M'

go
drop table StuffWithStatuses
drop table StuffWithPriorities
drop table LookupListValues
drop table LookupLists
go

-- The domains
create table Domains
(
	DomainID int primary key,
        DomainDesc varchar(100)
)
go

-- The domain constraints
create function fnCheckDomainValue(@DomainID int, @Value varchar(10))
returns bit
as
begin
	if @DomainID = 1	-- domain 1 only numeric 1-100
	begin
		if isnumeric(@Value) = 1 and @Value between 1 and 100
		return 1
	end
	else if @DomainID = 2	-- domain 2 only 3 pos char code
	begin
		if len(@Value) = 3 and @Value like('[a-z,A-Z][a-z,A-Z][a-z,A-Z]')
		return 1
	end
	return 0
end
go

-- The domain values
create table DomainValues
(
	DomainID int references Domains(DomainID),
	Value varchar(10) not null,
	ValueDesc varchar(100) not null,
	primary key(DomainID,Value),
	constraint chkValidDomainValue check( 1 = dbo.fnCheckDomainValue(DomainID,Value) )
)
go


-- A table that references Domain # 1
create table RefDomain1
(
	col varchar(10),
	MyDomain int default 1 check(MyDomain=1),
	MyValue varchar(10),
	constraint FK_MyDomain foreign key(MyDomain,MyValue) references DomainValues(DomainID,Value)
)
go

-- insert sample domains
insert Domains(DomainID,DomainDesc)
select 1,'1-100 domain' union all select 2,'3 pos char domain'

-- insert sample domain values
-- ok
insert DomainValues(DomainID,Value,ValueDesc) select 1,1,'...'
-- not ok
insert DomainValues(DomainID,Value,ValueDesc) select 1,101,'...'
-- not ok
insert DomainValues(DomainID,Value,ValueDesc) select 1,'g','...'

-- ok
insert DomainValues(DomainID,Value,ValueDesc) select 2,'abc','...'
-- not ok
insert DomainValues(DomainID,Value,ValueDesc) select 2,'000','...'
-- not ok
insert DomainValues(DomainID,Value,ValueDesc) select 2,'g','...'

-- insert table referrencing a domain
-- ok
insert RefDomain1(col,MyDomain,MyValue)
values('a',default,1) -- btw the default constrains the inserted value to 1 :)

-- not ok
insert RefDomain1(col,MyValue)
select 'a',2


select * from Domains
select * from DomainValues
select * from RefDomain1

-- cleanup
drop table RefDomain1
drop table DomainValues
drop table Domains
drop function fnCheckDomainValue