Linking

[Guest]

I have a recordset that has to be split up into several tables because it
contains too many characters. I plan on linking all the table through their
primary key in a 1-to-1 relationship.
My question is if this needs to be done in any particular way, ie. should
the tables be linked serially - table 2 is linked to table 1, table 3 to
table 2, etc, or radially, meaning all tables are linked to table 1? Does it
matter?

 

[Guest]

If your table has that many columns it is almost certainly not correctly
normalized and should be decomposed into a number of tables related in
one-to-many relationships.

One-to-one relationships are normally used to model a Type/Sub-Type. Chris
Date gives the example of Employees as a Type and Programmers as a Sub-Type,
with Application and System Programmers as Sub-Types of Programmers.

A Sub-Type shares all the properties of its (Super) Type but not of other
Sub-Types of that (Super) Type. Consequently if they were related by a
single table there would be columns irrelevant to some rows.

Such valid one-to-one relationships are directional in that the (Super) Type
has primacy, with the primary keys of its Sub-Types also being foreign keys.
So the relationship is really one-to-(one or zero) and the enforcement of
referential integrity should be applied accordingly.

I think you really need to examine your table with a view to decomposing it
into a number of normalized tables, rather than arbitrarily splitting it into
a number of tables merely on the basis of the number of columns.

One common source of an excessive number of columns in a table, and
something you should examine your table for, is what's known in the jargon as
'encoding data as column headings. This often takes the form of a large
number of Boolean (Yes/No) columns representing attribute of the entity which
the table is modelling. In this situation the columns should be replaced by
another related table where each column is represented by a row. Tall skinny
tables are usually the sign of a good design, while short fat ones of a bad
design.

Ken Sheridan
Stafford, England

 

[Guest]

That was very interesting, but I'm not sure it applies to my situation. It
may very well apply, but right now I don't see it.
Anyway, you did assume correctly that there are a large number of yes/no
fields, plus a few text and memo fields thrown in for good measure, but I
think I need those since what I am (re)creating is an application form where
there are a lot of questions of the "check all that apply" type, including
"other" fields that require text input. All of these have to be represented
on a form.
Right now most of the yes/no fields are in one table, and I may put most of
the text/memo fields in another, simply because I don't want to run into any
character limit issues.
I've been trying to come up with a good way to set this up, but I guess I
haven't been successful. And unfortunately, suggesting that I "normalize" the
db is a bit abstract for me as it assumes I know what to look for and how to
do this better - which I don't. So if you have any ideas, I'd be very glad to
hear them.
Thank you.

 

[aaron.kempf]

i disagree. sometimes it is necessary to have this many fields.

microsoft should build tools that support VWDB.. yes.. as in Very Wide
Database.

I've struggled with this before..

but do you know what the easiest solution is???

USE ACCESS DATA PROJECTS

you dont have as many limits on what you can do

-Aaron
ADP Nationalist

 

[Guest]

I'd strongly urge you to learn about the fundamental principles of how a
relational database works. Normalization is an important element in this and
provides a set of formal rules for testing the validity of the 'logical
model' i.e. the set of tables and how they are related which forms the model
of that part of the real world with which the database is concerned. There
is a vast amount of literature on this, some quite abstract and some more
easily digested. A very useful little book in the latter category is 'Inside
Relational Databases' by Mark Whitethorn and Bill Marklyn. I should declare
a small interest here as I periodically contribute to Mark's column in
Personal Computer World magazine. Bill Marklyn was the original Access
Development manager at Microsoft.

Google for Normalization and you'll find loads of stuff, but Ian Gilfillan's
article explains it well at:


http://www.databasejournal.com/sqletc/article.php/1428511


You have really confirmed what I suspected, that you are encoding data as
column headings by having a large number of Boolean columns in the table.
Thee data should be expressed as values in rows in a related table. Let me
give you a simple example which illustrates the principle:

Say you have a table of Suppliers who might be based in one or more cities.
For this example, which is purely to illustrate a principle, not a real life
scenario, we'll assume that no other address data per supplier is recorded.
You could do this in a single table in two ways:

1. You could have a table with columns C, City1, City2, City3, City4. This
creates a problem if a supplier is based in 5 cities, however, and also makes
it difficult when querying thr database as you have to look in 4 columns to
find a supplier in London for instance.

2. You could do it by having columns SupplierID and SupplierName and then
Boolean (Yes/No) columns London, Paris, New_York, Rome etc. If you then add
a new supplier in Tokyo say, you have to add a Tokyo column to the table, and
again querying is difficult, particularly if you want to aggregate the data.

The correct way to model this is to have 3 tables (1) Suppliers with
SupplierID and SupplierName columns; (2) Cities with columns CityID and City;
(3) SupplierCities with columns SupplierID and CityID.

The Cities table would have rows for London, Paris, New_York, Rome etc, so
adding Tokyo just means inserting a new row into the table, not changing the
table design.

The SupplierCities table models the many-to-many relationship between
Suppliers and Cities, so if Acme Widgets has SupplierID value 42 and is based
in London and New York, whose CityID values are 1 and 3. The SupplierCities
table would have one row with values 42 and 1 and another row with values 42
and 3 in its columns.

The above is not a complete model as city names can be duplicated, even
within a single country, but it illustrates the basics.

Your database seems to be a 'questionnaire' database. The above principles
apply in much the same way. You'd have a table of Questions and a table of
possible Answers with a foreign key which references the primary key of
questions. The questionnaire itself is a ternary (3-way) relationship
between the table of People and the Questions and Answers tables and is
modelled by another table. So if Person 9 selects Answer 2 and Answer 5 to
Question 7 the row in the table which models the relationship would have
values 9, 2,7 and 9,5,7. The 'other' category of answers would also have
rows in Answers referencing each question where that can apply and in the
questionnaire table would have a text or memo field for the free-form answer.
Take a look at the following for a simple diagrammatic illustration of this
sort of model:


http://www.microsoft.com/technet/prodtechnol/office/office2000/tips/accss00.mspx


Ken Sheridan
Stafford, England

 

[Guest]

That was very helpful, thanks a lot.

My "questions", however, are a bit different in that they are all check
boxes, ie. yes/no questions, that sometimes require text input as well [the
"other" option]. In other words, questions 1 - 100 can only be answered as
yes or no, or yes and user-generated text in some cases.

How do I deal with that? I didn't quite understand your idea, and the MS
article you so kindly linked to suggested putting a "response" yes/no field
into a table with the questionID and the personID. But I'd still have to
store the text somewhere.

 

[Guest]

The fact that your questions require just a yes or no answer doesn't make any
real difference; it simply means that there are only two possible answers
rather than four or five which might be the case with a multiple choice
question. Lets take one question as an example:

Have you stopped beating your wife?

The Questions table would have row for it such as:

QuestionID Question
--------------------------------------------------
42 Have you stopped beating your wife?

The Answers table would have three columns, the second being a text data
type, and for this question two rows such as:

AnswerID Answer QuestionID
----------------------------------
123 Yes 42
124 No 42

Using a text data type for Answer rather than a Boolean (Yes/No) data type
means that the table can hold answers to multiple choice questions as well as
simple Yes/No answers.

You'd have a table of Respondents, i.e. people answering the questions, so
if I'm dong so there'd be a row for me:

RespondentID FirstName LastName
----------------------------------------
666 Ken Sheridan

For the answers given to each question there would be a RespondentAnswers
table which models the relationship between the Respondents, Answers and
Questions tables and which includes a text or memo field for any
supplementary information. So if I answer Yes to the above question, but
qualify my answer with a comment there would be a row in the table:

RespondentID QuestionID AnswerID Notes
----------------------------------------------------------------
666 42 123 Starting again
soon.

You might also have a QuestionnaireID foreign key field in the last table
which references the primary key of a Questionnaires table which has columns
for the attributes relevant to the questionnaire as a whole such as its
title, date etc.

One important point about the above model is that the relationship between
the RespondentAnswers and Answers table should be on the two columns AnswerID
and QuestionID, and referential integrity should be enforced. This ensures
that the table can only accept rows where the AnswerID refers to a valid
answer to the question to which the QuestionID in the same table refers.

For data entry you would not have to enter the actual ID values into each
column in the RespondentAnswers table of course. On a form you'd have combo
boxes for RespondentID, QuestionID and AnswerID which list the text values
but are bound to the underlying columns of long integer number data type.
The user would then simply select a text value from each combo boxes list and
enter any comments into the Notes text box. This differs from the usual
layout of a paper questionnaire with check boxes of course, but that's simply
a reflection of the way that a relational database stores data in tables
which conform to the rigid 'rules' of the relational model, whereas the human
mind is able to cope efficiently with the expression of the same data in a
format which would be quite wrong and extremely inefficient in a relational
database.

So you can see how with a small number of tables having only a few columns
each you can handle any number of questions and answers whether these are
simply Yes/No answers or multiple choices. Moreover, in the case of the
latter, it will also handle multiple answers to one question of the type
'select one or more of the following with which you agree' type. By
correlating the QuestionID and AnswerID combo boxes the latter will only list
those answers which apply to the selected question; this is a little tricky
in a continuous form however as it need a 'hybrid' control made up of a text
box overlying a combo box so they look like a single combo box. I'd suggest
you concentrate on getting the logical model right before thinking about
these sort of refinements to the user interface however.

The ability of the database to handle both Yes/No and multiple answers to
questions doe have a small implication as far as data integrity is concerned,
because it would allow both Yes and No to be answered to the same Yes/No
question. In Access this can't really be controlled at the level of the
logical model, so reliance on enforcement in the user interface is necessary.
This reduction in application independence has to be accepted therefore. If
ALL the questions were of Yes/No type then it would simply require a unique
index on the RespondentID and QuestionID columns (and QuestionnaireID if
there is one) in the RespondentAnswers table.

Ken Sheridan
Stafford, England

That was very helpful, thanks a lot.

My "questions", however, are a bit different in that they are all check
boxes, ie. yes/no questions, that sometimes require text input as well [the
"other" option]. In other words, questions 1 - 100 can only be answered as
yes or no, or yes and user-generated text in some cases.

How do I deal with that? I didn't quite understand your idea, and the MS
article you so kindly linked to suggested putting a "response" yes/no field
into a table with the questionID and the personID. But I'd still have to
store the text somewhere.

 

[Guest]

Thank for this very comprehensive answer, I'm very grateful for you taking
all this time.

What you wrote made a lot of sense. I particularly enjoyed the realistic
scenario you used as an example.
However, I'm wondering if it might not be more efficient to use the
multiple-choice model only for the few questions that go beyond the yes/no
format and offer text input. Then I'd have to define answers for less than 10
questions, vs. over 100 if I did it for all of them. Of course then I'd be
back at my initial question regarding how to link tables that don't really
depend on each other but are parallel [if that makes sense].

Also, I would like to ask for clarification with regards to the
relationships between RespondentAnswers and Answers: "One important point
about the above model is that the relationship between the RespondentAnswers
and Answers table should be on the two columns AnswerID and QuestionID".
Am I correct to assume that this means the Answers table would have a
2-field primary key, AnswersID and QuestionID? If so, won't that be a problem
for establishing a one-to-many relationship between Questions and Answers?

 

[Guest]

I'd stick with the model as I described it. Inserting rows for 100+
questions and the corresponding 200+ rows for the yes/no answers is pretty
trivial in database terms. The principles behind the relational model are
mathematically solid and based on a vast amount of research, for which there
is a huge body of academic literature. Conforming with these principles is
very important if you are to avoid problems in the future, particularly when
it comes to analysis of the data via queries. The only time when you should
have a one-to-one relationship is when modelling a type/sub-type scenario as
I described in my original reply.

As regards the relationship between RespondentAnswers and Answers the
primary key of Answers is actually AnswerID. The combination of AnswerID and
QuestionID is obviously unique as anything combined with the key must be
unique, so it is a 'candidate key'. The reason for the two column
relationship is to allow referential integrity to be enforced. This means
that only an answer/question combination can be entered into
RespondentAnswers where that combination exists in the Answers table, i.e.
you can't record an a combination such as: Question: 'Have you stopped
beating your wife?' Answer: 'United Kingdom', because the later would be a
legitimate answer to another question such 'What is your country of birth?',
not the question asked.

Its easier to understand this if you think of a more classic model which
uses 'natural' keys rather than 'surrogate' numeric keys. In that case the
Answers table would not have an AnswerID column, but simply an Answer column
and a Question column; the Questions table would have just a Questions column
and the RespondentAnswers table would have an Answer column and a Question
column rather than AnswerID and QuestionID. In this model, which is really
the same one but using natural rather than surrogate keys, the Answer and
Question column WOULD be the primary key of Answers. You could in fact
extend the model further by calling this table QuestionAnswers and having a
separate Answers table with just one column Answer in which Yes and No would
appear once each and other row would have the other answers just once each,
some of them being possible answers to more than one question.

BTW if you are interested in the pros and cons of surrogate v. natural keys
the posts from the old CompuServe CASE forum on this were collated by Joe
Celko and can be found at:


http://community.netscape.com/n/pfx/forum.aspx?msg=19495.1&nav=messages&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

Thank for this very comprehensive answer, I'm very grateful for you taking
all this time.

What you wrote made a lot of sense. I particularly enjoyed the realistic
scenario you used as an example.
However, I'm wondering if it might not be more efficient to use the
multiple-choice model only for the few questions that go beyond the yes/no
format and offer text input. Then I'd have to define answers for less than 10
questions, vs. over 100 if I did it for all of them. Of course then I'd be
back at my initial question regarding how to link tables that don't really
depend on each other but are parallel [if that makes sense].

Also, I would like to ask for clarification with regards to the
relationships between RespondentAnswers and Answers: "One important point
about the above model is that the relationship between the RespondentAnswers
and Answers table should be on the two columns AnswerID and QuestionID".
Am I correct to assume that this means the Answers table would have a
2-field primary key, AnswersID and QuestionID? If so, won't that be a problem
for establishing a one-to-many relationship between Questions and Answers?

 

[Guest]

Ok, please tell me if I did this correctly:

tblAnswers has AnswersID as PK. That field is linked to AnswersID in
tblRespondentAnswers in a 1-to-many relationship with referential integrity
enforced.
At the same time, QuestionID in tblAnswers is linked to QuestionID in
tblRespondentAnswers in an indeterminate relationship. [Except Access changes
the link to go from tblRespondentAnswers/QuestionID to a new, automatically
created table tblAnswers_1/QuestionID]. There's no referential integrity
being enforced.
For all of this, tblRespondentAnswers doesn't have a primary key yet. My
inclination would be to make a new field for that, but maybe one or more of
the existing fields should be used for this purpose?

I'd stick with the model as I described it. Inserting rows for 100+
questions and the corresponding 200+ rows for the yes/no answers is pretty
trivial in database terms. The principles behind the relational model are
mathematically solid and based on a vast amount of research, for which there
is a huge body of academic literature. Conforming with these principles is
very important if you are to avoid problems in the future, particularly when
it comes to analysis of the data via queries. The only time when you should
have a one-to-one relationship is when modelling a type/sub-type scenario as
I described in my original reply.

As regards the relationship between RespondentAnswers and Answers the
primary key of Answers is actually AnswerID. The combination of AnswerID and
QuestionID is obviously unique as anything combined with the key must be
unique, so it is a 'candidate key'. The reason for the two column
relationship is to allow referential integrity to be enforced. This means
that only an answer/question combination can be entered into
RespondentAnswers where that combination exists in the Answers table, i.e.
you can't record an a combination such as: Question: 'Have you stopped
beating your wife?' Answer: 'United Kingdom', because the later would be a
legitimate answer to another question such 'What is your country of birth?',
not the question asked.

Its easier to understand this if you think of a more classic model which
uses 'natural' keys rather than 'surrogate' numeric keys. In that case the
Answers table would not have an AnswerID column, but simply an Answer column
and a Question column; the Questions table would have just a Questions column
and the RespondentAnswers table would have an Answer column and a Question
column rather than AnswerID and QuestionID. In this model, which is really
the same one but using natural rather than surrogate keys, the Answer and
Question column WOULD be the primary key of Answers. You could in fact
extend the model further by calling this table QuestionAnswers and having a
separate Answers table with just one column Answer in which Yes and No would
appear once each and other row would have the other answers just once each,
some of them being possible answers to more than one question.

BTW if you are interested in the pros and cons of surrogate v. natural keys
the posts from the old CompuServe CASE forum on this were collated by Joe
Celko and can be found at:


http://community.netscape.com/n/pfx/forum.aspx?msg=19495.1&nav=messages&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

Thank for this very comprehensive answer, I'm very grateful for you taking
all this time.

What you wrote made a lot of sense. I particularly enjoyed the realistic
scenario you used as an example.
However, I'm wondering if it might not be more efficient to use the
multiple-choice model only for the few questions that go beyond the yes/no
format and offer text input. Then I'd have to define answers for less than 10
questions, vs. over 100 if I did it for all of them. Of course then I'd be
back at my initial question regarding how to link tables that don't really
depend on each other but are parallel [if that makes sense].

Also, I would like to ask for clarification with regards to the
relationships between RespondentAnswers and Answers: "One important point
about the above model is that the relationship between the RespondentAnswers
and Answers table should be on the two columns AnswerID and QuestionID".
Am I correct to assume that this means the Answers table would have a
2-field primary key, AnswersID and QuestionID? If so, won't that be a problem
for establishing a one-to-many relationship between Questions and Answers?

 

[aaron.kempf]

Ken

NOT EVERY DATABASE _SHOULD_ BE NORMALIZED.

Where do you get off; dipshit is stuck in the 80s.

microsoft DOES need to expand support for VWDB - very wide databases

because sometimes there is a NEED to have 100 + fields.

I've been asking MS for this feature for a long long time:

a) give me all of the columns in a database in alphabetical order
select * {order by ABC} from myTable

b) give me every column from a table that starts with B
select * {like 'b%'} from myTable

Do i need to continue?

Microsoft; and this Ken Sheridan fag from England are TOO STUCK IN
THEORY to be practical.

there are a lot of times that you need to have a hundred fields; and
Microsoft SHOULD add just a little bit of functionality to support for
particular field names, etc

it's time to drop database theory and start allowing databases to be
more practical.

Dipshits like you are why we have dipshits using XML. Because someone
says 'oh but I need it to be more flexible'

Databases should be the answer to many problems; but because of
Dipshits like KEN SHERIDAN; Databases will be extinct-- because there
are too many limits and 'best practices'

MY BEST PRACTICE SAYS THAT YOU SHOULD LEAVE YOUR TABLE VERY-WIDE.

IF THAT IS HOW YOUR DATA IS SHAPED; LEAVE IT VERY WIDE.

think about the transaction side.

what if you properly normalize it.. and then you want to be able to
either commit or rollback 20 changes at the same time?

do you want a transaction with 20 subqueries; one to update each field?

ROFL

time for a dose of reality; I call for Microsoft to start building
support for VWDB.

it's PRESENT; it's NECESSARY; and it's NATURAL

 

[Guest]

You need to create just the one relationship between tblAnswers and
tblRespondentAnswers, on both the AnswerID and QuestionID columns. You can
do this in the Edit Relationship dialogue by selecting a second column for
each table. You'll probably have to change the primary key of tblAnswers to
AnswerID and QuestionID (as you surmised in your last post). Its one of the
effects of using a surrogate autonumber column in situations where it is not
absolutely necessary. I'd forgotten that Access will create an indeterminate
relationship if you do it on a candidate key rather than the specified
primary key in the referenced table. Apologies for any confusion this caused.

As regards the tblRespondentAnswers table there is not really any point in
adding a surrogate key column to this as its not referenced by any other
table. You can make the RespondentID, QuestionID and AnswerID columns the
primary key, though this assumes the respondent answers a question only once.
In a situation where the same questionnaire might be answered by the same
respondent on different occasions, e.g. a political opinions focus group,
then you'd need to include another column such as QuestionnaireID referencing
a Questionnaires table with columns QuestionnaireID, QuestionnaireTitle,
QuestionnaireDate etc., or simply a DateAnswered column.

The tblAnswers_1 table which appears in the relationship window is just a
second instance of the same table BTW. Its not a new table, just the same
one with a suffix to distinguish it in the second relationship created.

 

[Guest]

Thank you, your patient help has been invaluable. I believe the design is
finally done; now I can start working on the queries and forms.

Btw, this "questionnaire" of mine is not really something for actual people
to answer, it's a checklist for setting up classes, so the "respondents" will
always be unique as this has to be done. Well, maybe in the future they'll
want to recycle existing application information, but not yet.

You need to create just the one relationship between tblAnswers and
tblRespondentAnswers, on both the AnswerID and QuestionID columns. You can
do this in the Edit Relationship dialogue by selecting a second column for
each table. You'll probably have to change the primary key of tblAnswers to
AnswerID and QuestionID (as you surmised in your last post). Its one of the
effects of using a surrogate autonumber column in situations where it is not
absolutely necessary. I'd forgotten that Access will create an indeterminate
relationship if you do it on a candidate key rather than the specified
primary key in the referenced table. Apologies for any confusion this caused.

As regards the tblRespondentAnswers table there is not really any point in
adding a surrogate key column to this as its not referenced by any other
table. You can make the RespondentID, QuestionID and AnswerID columns the
primary key, though this assumes the respondent answers a question only once.
In a situation where the same questionnaire might be answered by the same
respondent on different occasions, e.g. a political opinions focus group,
then you'd need to include another column such as QuestionnaireID referencing
a Questionnaires table with columns QuestionnaireID, QuestionnaireTitle,
QuestionnaireDate etc., or simply a DateAnswered column.

The tblAnswers_1 table which appears in the relationship window is just a
second instance of the same table BTW. Its not a new table, just the same
one with a suffix to distinguish it in the second relationship created.

Ok, please tell me if I did this correctly:

tblAnswers has AnswersID as PK. That field is linked to AnswersID in
tblRespondentAnswers in a 1-to-many relationship with referential integrity
enforced.
At the same time, QuestionID in tblAnswers is linked to QuestionID in
tblRespondentAnswers in an indeterminate relationship. [Except Access changes
the link to go from tblRespondentAnswers/QuestionID to a new, automatically
created table tblAnswers_1/QuestionID]. There's no referential integrity
being enforced.
For all of this, tblRespondentAnswers doesn't have a primary key yet. My
inclination would be to make a new field for that, but maybe one or more of
the existing fields should be used for this purpose?