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
