Help needed on planning an SQL database

I'm a student learning SQL and PHP, I have been set a task to create a student feedback form using PHP and mySQLi, and im really stuck at getting my head around how to design the database for the project!

I'm creating a system where users can log into a web page, and if the user is a student, they can see a page to leave feedback for each lesson had for each mo开发者_C百科dule (class). If they are a tutor, when they log in they can view the feedback, add modules with the amount of lessons there will be and add student accounts.

I don't understand which would be the best way to create the tables for the database, especially as I understand you cannot put a table within a table, I was going to have a table for modules and within each module would be a table for each lesson which is a table holding each item of feedback and a few other variables such as an additional message.

How can I structure my database without creating hundreds of tables and trying to link them together, as I'm new to SQL and this seems a very long winded untidy way.

I've been trying to get my head around databases for days now and I just don't know what to do!

Database tables can have relationships to each other that mimic what you describe as a nested table. In database parlance there are 1 to 1 relationships, 1 to many relationships and many to many relationships. If you wanted to have many classes for a single Module you could have a field in the Class table that holds a foreign key to the Module record to which it belongs. (Many Classes to 1 Module).

If you want to link many students to a single class and have many classes for a single student you typically use a separate table that just holds the foreign keys to both tables. That is you have a Student_2_Class table and it has a field for Class_FKey and a field for Student_FKey, sometimes these many-to-many linking tables have additional fields when needed. At a minimum you would add a constraint that the combination of the two foreign keys are unique. That way you can't put the same student in the same class more than once.

I don't think I can teach you how to design databases in a thread response but you should be able to search on terms like primary key, foreign key, many to one, one to many, many to many etc to find things. Also your school library may have books on "Data Modeling" that might help you (I liked Simsion and Witts Data Modeling Essentials. Visio has a decent Entity Relationship Diagram tool (database models are often called entity relationship diagrams) so you also might be able to find online tutorials showing how to use it and you might pick up some modeling tips just by watching.

You shouldn't need hundreds of tables! You will, however, need to link them using primary keys. This is the essence of SQL, and newbie or not, that's exactly what you need to learn.

In essence, you are putting a table inside a table, but you link them together by matching keys:

student_id  student_name
1           Bob
2           Jane

comment_id  student_id  comment_body
123         2           Jane says hi!
124         1           Bob says hi also!
125         1           Bob says hi again!

When you query these tables, you connect them together in your SELECT statement:

SELECT student_id, student_name, comment_id, comment_body
FROM student, comment
WHERE student.student_id = comment.student_id


student_id  student_name  comment_id  comment_body
2           Jane          123         Jane says hi!
1           Bob           124         Bob says hi also!
1           Bob           125         Bob says hi again!

The rows you get back are a combination of the two tables, connecting (or JOINing) the two together wherever the primary key (in this case, student_id) is equal in both tables.