RDBMS question on coding expanding series-like fields
Hi! I have a Test Questions database. Right now I have a hard-coded limit of 200 questions in it; I actually made a table with field names like Quest1, Quest2. . . Quest200. However, I know that I am not using the power of MySql in setting it up this way. This is more an example of my own ignorance than of the power of MySql! Can someone explain how to set up a table with a fieldname that 'expands' (maybe something like Quest[i] where "i" can be incremented as required). Is there more than one way of doing this? Is there a place where I might see some sample code? I did look up 'enum' and 'set' in the manual but I don't feel confident with my grasp of the limited explanations given of these. My feeling is that perhaps 'enum' would be a candidate for what I need, as 'set' has a limit on how big the set can get.I would like to have the possibility of data expansion as needed. Any tips whatever would be appreciated! Thanks very much! Cheers! -Warren - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RDBMS question on coding expanding series-like fields
Hi, I have a Test Questions database. Right now I have a hard-coded limit of 200 questions in it; I actually made a table with field names like Quest1, Quest2. . . Quest200. However, I know that I am not using the power of MySql in setting it up this way. This is more an example of my own ignorance than of the power of MySql! Can someone explain how to set up a table with a fieldname that 'expands' (maybe something like Quest[i] where "i" can be incremented as required). Is there more than one way of doing this? Is there a place where I might see some sample code? What you need is to do something called 'Normalising' (read any good RDBMS book to find out exactly what this is). In your case, two seperate tables is what you need. One for tests and one for the questions for those tests. Here's a quick example: Tests: * Test ID * Name Questions: * Question ID * Question * Test ID Each record in the Questions table not only has it's own unique identifier, but is also related to a particular record by the Test ID field. This is called a 'One-to-Many' relationship - one question record is related to many answer records. Therefore, if we want to have two tests with two questions each, then the table data would look like this: Tests: * 1, "US Culture" * 2, "The Alphabet" Questions: * 1, "Who's the president?", 1 * 2, "Which company does Bill Gates work for?", 1 * 3, "Which letter comes after F?", 2 * 4, "What's the last letter?", 2 So, if you structure your data like this, you'll be able to have not only an unlimited number of tests, but an unlimited number of questions for each test. I did look up 'enum' and 'set' in the manual but I don't feel confident with my grasp of the limited explanations given of these. My feeling is that perhaps 'enum' would be a candidate for what I need, as 'set' has a limit on how big the set can get.I would like to have the possibility of data expansion as needed. Enum column types are only ever useful for fields that will only ever contain one of a fixed set of values - like "Yes" and "No", for example. I never use Sets - they're evil. ;) Regards, Basil Hussain ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RDBMS question on coding expanding series-like fields
You need to re structure it using a table called say Questions with the following sorts of fields QID Id number QTitle Title eg Question1 Q The question text A The answer text R The rank or score of the question That way you add questions simply by adding records to the table. you might find the tutorials at this site useful http://www.rd-robotics.com/accesscommunity/tutes/index.html although it is based on access I found it useful. -Original Message- From: WCBaker [SMTP:[EMAIL PROTECTED]] Sent: 27 February 2001 16:25 To: MySQL Subject: RDBMS question on coding "expanding series-like fields" Hi! I have a Test Questions database. Right now I have a hard-coded limit of 200 questions in it; I actually made a table with field names like Quest1, Quest2. . . Quest200. However, I know that I am not using the power of MySql in setting it up this way. This is more an example of my own ignorance than of the power of MySql! Can someone explain how to set up a table with a fieldname that 'expands' (maybe something like Quest[i] where "i" can be incremented as required). Is there more than one way of doing this? Is there a place where I might see some sample code? I did look up 'enum' and 'set' in the manual but I don't feel confident with my grasp of the limited explanations given of these. My feeling is that perhaps 'enum' would be a candidate for what I need, as 'set' has a limit on how big the set can get.I would like to have the possibility of data expansion as needed. Any tips whatever would be appreciated! Thanks very much! Cheers! -Warren - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RDBMS question on coding expanding series-like fields
Hi Warren,
What I personally would do is simply include some sort of 'ID' field. In
other words, each question would have a unique ID. Question 1's ID would be
1, Question 2's ID would be 2, etc. Or however you wanted to number them.
You could even set this up as an auto_increment and have the next number in
sequence automatically generated when a new question is inserted into the
database.
Then, if you want to call something "Question 1", in your programming
language, simply prepend Question to the ID field. If I were doing this in
Perl, for example, I would do:
print "Question " . $db_query_hash-{'id'};
#where $db_query_hash is the hash of field names and values returned from the
#query, and 'id' is the field containing the id of the question.
The above example would output "Question 1" if the ID selected from the
database were 1.
The main point here is that putting a value like "Quest1" in a database is
(in my opinion) redundant. Simply make the name of the field "Question" and
make the type of the field "num".
Of course I have not been using MySQL that long either, so perhaps some
wizard will have a much smarter answer. :-)
Peter
On Tuesday 27 February 2001 11:24 am, you transmuted into bits these words:
Hi!
I have a Test Questions database. Right now I have a hard-coded limit of
200 questions in it; I actually made a table with field names like Quest1,
Quest2. . . Quest200. However, I know that I am not using the power of
MySql in setting it up this way. This is more an example of my own
ignorance than of the power of MySql!
Can someone explain how to set up a table with a fieldname that 'expands'
(maybe something like Quest[i] where "i" can be incremented as required).
Is there more than one way of doing this? Is there a place where I might
see some sample code?
I did look up 'enum' and 'set' in the manual but I don't feel confident
with my grasp of the limited explanations given of these. My feeling is
that perhaps 'enum' would be a candidate for what I need, as 'set' has a
limit on how big the set can get.I would like to have the possibility
of data expansion as needed.
Any tips whatever would be appreciated!
Thanks very much!
Cheers!
-Warren
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED] Trouble
unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
