mysql maximum table

Hi,

Just curious abt this stuffs

1. Is there any maximum tables that is limited to Mysql ?
2. can a mysql database holds up tp app 4000 to 5000 tables in each database?
3. If there is over 1000 tables in mysql, can phpmyadmin be used efficently to manipulate those database ?


Thanks , hope to read something soon.

I think you'll have problems managing over 100 tables no matter what tool you use.

What exactly do you need so many tables for?!

Hadley

Ah, just found something.

You may want to read: http://forums.devshed.com/archive/4/2002/04/3/33073

(found by searching google for maximum tables mysql)

Hadley

i am designing db to hold students assignment grades of a school, where in the school there are 3 levels, namely 1, 2 and 3.
each level has 8 sections with max of 25 students in each section. Each section takes abt 7 to 8 course.
In each course the final grading is done based on few categories, i.e. assignments, quizes, mid terms, and final exams. assignments and quizes varies according to the instructor.
each assignment to be submitted has options , thats submission can be done on line ( web based ) or manually direct to the instructor.

each of those work( assignment, quizes, mid terms and final exam) , the submission must be recorded, i.e when date, marks, comment, filename .

my design consists of:

1 main table, registering all the courses, ( regardless sections and level)
for each course, there is one table hold the assignment information i.e. date of assigment, question, answer, teacher, max mark, and penalty marks ( since the system must be able to deduct mark for late online submissions )
and each assignment got 1 table to record the marks.

there for if there is 10 assignments in each class

10 * number of courses * number of sections * number of levels

10 * 8 * 8 * 3 = 1920 tables just for assignments marks.

I been thinking a way to store all the assignment marks in one big table, but that seems to be impossible since i have to record the submission date, and their respecting filenames .. any idea ?

thanks

there is alternative way , thats to have 1 commong mark table for everyone, regardless their class, by having the course ref, assignment ref , but

for appr 10 assignments ( it could be more )
10 * 8 *8 * 3 * 25 =48,000 entries in that table alone.

( since we have space to hold the max. size supported by mysql app 4 G ) but will that table alone is overloaded ? will searching and displaying will be little slower die to the big data ...

finally we have generate a summary for each sections, and the query could be little complex ...

hope to hear from anyone ..
thanks

Hi ..

Finally I decided on the following design to store the assignment information

1. one table for all course ,
2 each course will have two tables
a) Assignment table , information abt the assignments
b) Mark table, all marks , regardless of assignment number is
stored here

there for, the number of tables will be reduced greatly.

the mark table will have abt :
25 students * 15 assigments = 375 entries, which is relativly a small enty ,

alternatively i could store all the marks, regardless course , section and level in 1 table, but the entry could reach up tp 10000 entries, which I am curious will effect the queries ...
any idea ?

I would strongly suggest that you use the minimum tables possible, you will find it much easier to extract the data and compare it in different ways.

In this case I would suggest you need a table for

* the list of students
* the list of courses
* the assignments, with a reference to the course table to indicate which course it is for
* the assignment results, with a reference to the assignment table to indicate for assignment the mark is for, and a reference to the student table to indicate who the mark is for

Don't worry about the number of records in each table. As long as you are running a reasonable fast computer, mySQL can deal with hundreds of thousands of records easily.

You may want to read more about good database design before starting this project as it will make things a lot easier in the long run.

Hadley

i am runing a database with more than 2k tables without any problem it is not slow or anything, i am sure there is no limit if you do it on a cluster, but it is kind of slow if you browse it with a basic utility like phpmyadmin or navicat.