table names and quantity

[ketanco]

two basic questions:
1-can mysql table name be a number (based on a $variable)?
and
2-is it better to have less number of bigger tables (around may be 50 or so) or more smaller tables (hundreds of them) in one database. please compare in terms of performance, backup or anything else you think important.
the issue is, i am writing a poll and trying to decide if there should be an answer table for each poll, or there should be less answer table containing more than one polls answers.

if table for each poll is better, i will create it automatically by giving the table name the poll's $id number from the question table, so the answer table's name for just that poll will be a number, same number as the id of that poll in the table containing the polls (that one is just one table, listing all the polls by their id, which has all the questions).

if less tables are better, i will then need to periodically go in manually and update which table the answers are going in to, as they will get large quicker, by receiving all answers from many polls...i prefer this less..

thanks

[lochgo]

1-can mysql table name be a number (based on a $variable)?


According to the MySQL 5.1 documentation (http://dev.mysql.com/doc/refman/5.1/en/identifiers.html),

Identifiers may begin with a digit but unless quoted may not consist solely of digits.



2-is it better to have less number of bigger tables (around may be 50 or so) or more smaller tables (hundreds of them) in one database. please compare in terms of performance, backup or anything else you think important.
the issue is, i am writing a poll and trying to decide if there should be an answer table for each poll, or there should be less answer table containing more than one polls answers.

if table for each poll is better, i will create it automatically by giving the table name the poll's $id number from the question table, so the answer table's name for just that poll will be a number, same number as the id of that poll in the table containing the polls (that one is just one table, listing all the polls by their id, which has all the questions).

if less tables are better, i will then need to periodically go in manually and update which table the answers are going in to, as they will get large quicker, by receiving all answers from many polls...i prefer this less..

thanks

This is extremely situational. What is "best" is determined by your specific needs. What it sounds like to me though is that you will be making quite a few database calls. I recommend having one table for the answers for all polls. Especially if you will be adding polls frequently. But that is my opinion. You will need to look at your situation to see what will work best for you.