|
|
Posted by Mark on 07/14/06 15:06
Hi there gurus, can you please add your 2 cents on this design? We're
having trouble relating these tables in a diagram because of the keys.
Is it necesary to have the references setup? I would assume yes so the
forign keys can be setup.
If you look at this link, you'll see our diagram. In Red are the
relationships that we would like to make for referential integrity, but
cannot because of the keys.
http://rullo.ca/linktome/QuestionsDB.jpg
Our goal in all of this is to have a facility wherin we can store a
question, that has multiple names over multiple Languages. For
instance:
-Q1| QNameID = 1 | "Do you have a dog in your appartment?" | LangID =
1(eng)
-Q1| QNameID = 2 | "Do you have a dog in your house?" | LangID =
1(eng)
-Q1| QNameID = 1 | "-French - Do you have a chien in your appartment?"
| LangID = 2(fr)
-Q1| QNameID = 2 | "-French - Do you have a chien in your house?" |
LangID = 2(fr)
The difficulty is when we try and put this in the group details table.
We don't want to outline the Language, we'd just pass the language into
a proc to retreive a specific group with a specific language. If you
folks would be so kind as to add your comments to the design I would be
truely grateful.
CREATE TABLE [Question] (
[QuestionID] [int] NOT NULL ,
[SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED
(
[QuestionID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [QuestionAnswer] (
[QuestionID] [int] NOT NULL ,
[QuestionAnswerID] [int] NOT NULL ,
[SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_QuestionAnswer] PRIMARY KEY CLUSTERED
(
[QuestionID],
[QuestionAnswerID]
) ON [PRIMARY] ,
CONSTRAINT [FK_QuestionAnswer_Question] FOREIGN KEY
(
[QuestionID]
) REFERENCES [Question] (
[QuestionID]
)
) ON [PRIMARY]
GO
CREATE TABLE [QuestionAnswerName] (
[QuestionAnswerID] [int] NOT NULL ,
[QuestionAnswerNameID] [int] NOT NULL ,
[LanguageID] [int] NOT NULL ,
[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_QuestionAnswerName] PRIMARY KEY CLUSTERED
(
[QuestionAnswerID],
[QuestionAnswerNameID],
[LanguageID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [QuestionGroup] (
[QuestionGroupID] [int] NOT NULL ,
[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_QuestionGroup] PRIMARY KEY CLUSTERED
(
[QuestionGroupID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [QuestionGroupDetails] (
[QuestionGroupID] [int] NOT NULL ,
[QuestionNameID] [int] NOT NULL ,
[QuestionAnswerNameID] [int] NOT NULL ,
[QuestionSortOrder] [int] NULL ,
[AnswerSortOrder] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[DisplayLevel] [int] NULL ,
CONSTRAINT [PK_QuestionGroupDetails] PRIMARY KEY CLUSTERED
(
[QuestionGroupID],
[QuestionNameID],
[QuestionAnswerNameID]
) ON [PRIMARY] ,
CONSTRAINT [FK_QuestionGroupDetails_QuestionGroup1] FOREIGN KEY
(
[QuestionGroupID]
) REFERENCES [QuestionGroup] (
[QuestionGroupID]
)
) ON [PRIMARY]
GO
CREATE TABLE [QuestionNames] (
[QuestionID] [int] NOT NULL ,
[QuestionNameID] [int] NOT NULL ,
[LanguageID] [int] NOT NULL ,
[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Desciption] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ControlTypeID] [uniqueidentifier] NOT NULL ,
CONSTRAINT [PK_QuestionNames] PRIMARY KEY CLUSTERED
(
[QuestionID],
[QuestionNameID],
[LanguageID]
) ON [PRIMARY] ,
CONSTRAINT [FK_QuestionNames_Question] FOREIGN KEY
(
[QuestionID]
) REFERENCES [Question] (
[QuestionID]
)
) ON [PRIMARY]
GO
Navigation:
[Reply to this message]
|