What is the best way to setup these tables in MySQL?

[Glenn]

I am creating a web site that will be used by a group of students. Hopefully, eventually, a very large number of students. Basically, they will be creating notes in this database. Each user will have his own set of subjects, topics, and notes. An amount of 10 to 100 notes in each topic and several topics in each subject. I had first thought after creating the users subject list, each subject would have it's on table of topics, and each topic would have its own table of notes. So, each topic table would have the name "username_subject_topic".

Would I be better off having one table with columns of "subject" and "topic" and just sort them out as they use them? They would only be pulling up one topic at a time. Not sure they all needed to be in the same table. But, I also hate having that many tables.

Not sure if I have given enough into or not. Please advise.

 

[DesignerBill]

You definitely need 4 separate tables for the students (users), subjects, topics, and notes. That is how you create a properly normalized and maintainable database. Be sure to store information pertaining to the type of entity only in each table. For example, don't include student name and other student information anywhere except in the student table.

Create a unique numeric id for each row when adding entities, and use these id's only for cross reference between tables.
For example, use subject id, not subject name, to cross reference topics to subjects. Use topic id to cross reference notes to topics.

You probably want to include a student id in all tables so each student can quickly pull up their own stuff, and so when a student no longer exists, you can delete all their history.

Sounds like an interesting project. Hope it works out well for you.