By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,706 Members | 2,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,706 IT Pros & Developers. It's quick & easy.

Table design request for comment. (Help please)

P: n/a
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

Jul 14 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Posted in another group, hope this further explains what we need.
>
You have not provided any background information or any functional details
of the business model and rules. Without that others cannot meaningfully
comment on a specific logical design.

--
Anith
I'll try and expand on the business requirements as requested.

Our goal in all of this is to have a facility wherin we can store
questions and the associated answers. We need to have multiple names
for these questions and answers over multiple Languages.

For each question ID (one question) we want to be able to ask it or
display it differently(n ways) (see above example).

The same requirement goes for each answer. We want to have multiple
"names" for one answer over multiple languages. For instance

QueAnsID = 1 | AnsNameID = 1 | LangID = 1(en) | "Yes"
QueAnsID = 1 | AnsNameID = 2 | LangID = 1(en) | "Y"
QueAnsID = 1 | AnsNameID = 1 | LangID = 2(fr) | "Oui"
QueAnsID = 1 | AnsNameID = 2 | LangID = 2(fr) | "O"

QueAnsID = 2 | AnsNameID = 3 | LangID = 1(en) | "No"
QueAnsID = 2 | AnsNameID = 4 | LangID = 1(en) | "N"
QueAnsID = 2 | AnsNameID = 3 | LangID = 2(fr) | "Non"
QueAnsID = 2 | AnsNameID = 4 | LangID = 2(fr) | "N"

This is so that when it comes together from the above example, we'd
pass in a groupID and a LanguageID into a proc and the result would be:

Do you have a dog in your appartment?
Yes
No

Or, if the group were different:

Do you have a dog in your house?
Y
N

I think the trick is not to put the LanguageID in the groupDetails
table so that the LangID can just be passed into the proc, and the
appropriate question / answer for that language is returned.

I hope this helps with the requirements.

Jul 14 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.