quote:

---

One last question. In regards to these "look-up" tables, is it a good idea to have a seperate table for each "look-up" type (even if that means having a large number of these tables?)

---

Questions
QuestionGroup   Question     QuestionText
WC              Contact      Are you the authorized account contact?
WC              Address      What is the authorized address for your account?
WC              Units        How many units did you purchase?

Answers
QuestionGroup   Question     Answer         AnswerText
WC              Contact      Yes            Yes, caller is authorized account contact
WC              Contact      No             No, caller is just some schmuck
WC              Address      Yes            Yes, caller has correct address
WC              Address      No             No, caller doesn't know correct address
WC              Units        1              One unit
WC              Units        2              Two units
WC              Units        3              Three units
WC              Units        4              Four units
WC              Units        5              Five units
WC              Units        6+             Six or more units

CustomerResponses
CustomerName   QuestionGroup   Question     Answer
Joe Smith      WC              Contact      Yes
Joe Smith      WC              Address      Yes
Joe Smith      WC              Units        5

--"Code Table"
CREATE TABLE Domains (Domain VARCHAR(35) NOT NULL, Value SQL_VARIANT NOT NULL CHECK(SQL_VARIANT_PROPERTY(Value,'TotalBytes') <= 865), 
	IsDefault BIT NOT NULL DEFAULT(0), Constraint PK_Domains Primary Key(Domain, Value))
go
--Integrity Function
CREATE FUNCTION fnIsDomain
(@Domain VARCHAR(35),
@Value SQL_VARIANT)
RETURNS BIT
AS
BEGIN
DECLARE @BIT BIT
SET @BIT = 0
IF EXISTS(SELECT 1 FROM Domains WHERE Domain = @Domain and Value = @Value)
	SET @BIT = 1
RETURN @BIT
END
GO
--Default generator
CREATE FUNCTION fnDefault
(@Domain VARCHAR(35))
RETURNS SQL_VARIANT
AS
BEGIN
RETURN (SELECT Value from Domains where Domain = @Domain and IsDefault = 1)
END
go
--Domain data (Gender is not a good example but you get the picture)
INSERT TABLE Domains (Domain, Value, IsDefault)
	SELECT 'Gender','M',0
	UNION ALL
	SELECT 'Gender','F',0
	UNION ALL
	SELECT 'Gender','?',1
GO
--In use...
CREATE TABLE Person (PersonID int NOT NULL Primary Key, Gender CHAR(1) NOT NULL CHECK(dbo.IsDomain('Gender',Gender) = 1)
DEFAULT(CAST(dbo.fnDefault('Gender') AS CHAR(1))
GO