473,320 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Table design request for comment. (Help please)

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
1 1492
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Danimal | last post by:
I have been using PHP for a long time... since it was called PHP/FI. I have a programming design question: Let say I have this class: class attrib { var $lenght; var $type; ... }
2
by: TomHorner | last post by:
I have several quick questions about reorg's, and a request (favor). 1. The documentation says that reorg "Reorganizes an index or a table" I cannot see how one would reorg ONE particular index...
5
by: Ilan Sebba | last post by:
When it comes to adding records in related tables, Access is really smart. But when I try to do the same using ADO, I am really stupid. Say I have two parent tables (eg Course, Student) and one...
3
by: simonmarkjones | last post by:
Hi i'm able to update a table using the following code and increment a number stored. myNumber = DMax("", "tableLastUsedNumber") + 1 strSQL = "UPDATE tableLastUsedNumber SET BCCCC = " & myNumber...
12
by: Sunny | last post by:
Hi All, I have a serious issue regarding classes scope and visibility. In my application, i have a class name "TextFile", and also a few other classes like "TotalWords", "TotalLines" and etc..,...
6
by: Ammar | last post by:
Dear All, I'm facing a small problem. I have a portal web site, that contains articles, for each article, the end user can send a comment about the article. The problem is: I the comment length...
3
by: McKirahan | last post by:
I have a Web page that uses some JavaScript and CSS. I have specified a three column table with a fixed-width left and right side; the center colum expands to fill the page. Within this...
5
by: Nirmala123 | last post by:
hi... I want to sort the table using combobox values. I give the code here. address.html: <html> <head> <title>Add a new entry</title> </head>
1
oranoos3000
by: oranoos3000 | last post by:
hi i have an error with enter record to table in database(mysql) structure of my table is as follow - phpMyAdmin SQL Dump -- version 2.11.6 -- http://www.phpmyadmin.net -- -- Host:...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.