Organizing MySQL databases and tables

I would like some advice about organizing MySQL.

I have only 1 database at the moment: allstudentsdb

That includes 7 different classes, 22 different tables, all in all 289 students. Tiny I suppose, but I already get confused.

I could integrate some tables, but that means more columns, less oversight.

If I get a new course to manage, should I make a new database for each course, or add more tables to my current allstudentsdb??

Then I thought about commercial applications, where a company might have thousands and thousands of customers. Do all the customers go in 1 database?

Any web site should have a single database. An unlimited number of tables can be added to any database.

Based on the information you have provided up to this point, your data is not normalized and you are manually creating new tables for each new class and major period of time. This is not general-purpose, reusable, and is not using the computer as a tool to accomplish a task. You have taken an activity done in the past using paper records and digitized it, but you are still spending time creating, moving, and maintaining the pieces of digital information yourself.

You would need to provide a specific example of what you mean and why you think there would be a problem.

If you have successfully designed your database and code, all this would involve is inserting a new row in a ‘course’ table, which would then create a new course_id value, then insert new rows in a course_student table, with the new course_id and the student_id values for the students registered for that course. This would then create new course_student_id values that would be used to recorded any data related to a specific student in a specific course.

Any code dealing with displaying or inserting data for any course should then ‘automatically’ adapt to the new course, simply based on the existence of the record being in the ‘course’ table.

They all go in one (logical) table. With today’s server hardware, a large table starts at 5 million rows. Table Partitioning is used to divide very large tables in to smaller parts. See this link - https://en.wikipedia.org/wiki/Partition_(database)

Sponsor our Newsletter | Privacy Policy | Terms of Service