If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
2 primary keys
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 |
#2
|
|||
|
|||
2 primary keys
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/f...osts.asp?TID=3 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Fred Blair" wrote in message ... 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 |
#3
|
|||
|
|||
2 primary keys
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 "Douglas J. Steele" wrote in message ... 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/f...osts.asp?TID=3 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Fred Blair" wrote in message ... 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 |
#4
|
|||
|
|||
2 primary keys
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 "Fred Blair" wrote: 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 "Douglas J. Steele" wrote in message ... 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/f...osts.asp?TID=3 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Fred Blair" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|