2 primary keys

[Fred Blair]

In one of the examples that are shown on tutorial pages that are shown in
this group, show a relationship that shows a table with 2 primary keys. How
can you do that?

I have two tables. TestMaster and TestQuestions

Test Master has three fields;
testid (autonumber)(primary key)
testname(text)
# of questions(int)
date modified(date).

TestQuestions has 8 fields:
QuestionNum(autonumber)(primary key)
testid(an integer that is linked back to the testid in the Testmaster table)
Questiontext(text - the actual text of the question)
Option1(text-the text of the first option of a multiple choice question)
Option2(text for the second option)
Option3(text for the third option)
Option4(text for the fourth option)
currectanswer(text - the correct choice)
date modified(date)

Each test will have 50 questions and it is a multiple choice test with 4
options.

I created a relationship between the two tables linking testid in both
tables and a simple query retrieves the correct data.

Is there a way to actually call the testid in the TestQuestions table a
primary key or does linking the two variables together in the relationship
document do the same thing?

For later questions, is this an ok way to display the table items?


Fred

 

[Douglas J. Steele]

By definition, a table has only one primary key (which, in Access, can have
up to ten different fields making it up)

If you were to create a unique index on testid in table TestQuestions, you'd
be limited to having a single question for each test!

Incidentally, your table design is definitely suboptimal. Having four fields
like Option1, Option2, Option3 and Option4 in a single table is called a
repeating group, and is a violation of the first database normalization
rule. To see a properly designed database for this sort of thing, see what
Duane Hookom has at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

 

[Fred Blair]

I also realized that the second table will not work, since questionNum can
not be unique because I will use the same question numbers for the second
testid. I will look at Rogers table that you referenced. I did not think
the Opt1 - Opt4 would not be a problem since the multiple choice part will
always include 4 possible responses.

Fred
Beginner

 

[Ken Sheridan]

Fred:

The essential point here is that Option1, Option 2 are not the names of
'attributes', which is what columns represent, but are really data values of
one attribute, Option. There is a fundamental principle of the database
relational model known as the Information Principle. It was E F Codd's Rule
1 (out of 13) when he first proposed the model back in 1970 (there was also a
Rule 0) and is as follows:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date - Introduction to Database Systems; 7th Edition; 2000

What you've done (and it’s a common mistake) is what's known as 'encoding
data as column headings'. This breaks the above rule which requires all data
to be stored as 'explicit values in column positions in rows in tables'.

And as Doug has pointed out it also in effect means the table is not
normalized to First Normal Form (1NF). Normalization is the process of
eliminating redundancy from a database, and thus eliminating the risk of
inconsistent data. There are normal forms 1 to 5, plus another one inserted
when it was realised one of them didn't cater fro a particular scenario.
INF is formally defined as:

First Normal Form: A relvar is in 1NF if and only if, in every legal value
of that relvar, every tuple contains exactly one value for each attribute.

Loosely speaking in relational-speak a relvar equates to a table, a tuple to
a row (record) and an attribute to a column(field).

Some people would say 'so what, it works!', but then, after entering a lot
of data they suddenly find they can't query the database in some way they
want to because of the flawed design. The rules have been devised and
refined over many years of research and exist for good reason.

Ken Sheridan
Stafford, England