selecting tables, with variables

[Qbasicboy]

I\'m a bit new to MySQL. I\'ve been trying to do this, but with no luck. I have several tables within a database for each user. For instance, I have username_grade, username_teacher, username_etc for specific information inside those tables. My question however, is how to create a mysql querry to only select tables using the username attribute. for instance, when a user wnats to see all the tables he has, only the tables for his username show up. Any ideas? thanks

[gizmola]

Could you describe why you would have bunch of tables with different people\'s \"Usernames\" in the tablename? Is this for educational purposes... for a school where students will be learning using tables? If so, each student should have their own database, and in that case, the tables will all have the same names.

 

If there\'s some other reason for this design, I suspect you\'ve gotten off on the wrong track.

[llama]

I can see the logic behind this set up. But alternatively couldn\'t you have a table for each of the 3 areas (grade/teacher/etc?) and then each row be a different user? The only time this isn\'t suitable is if each username can have many grades/teachers/etc attached to it.

 

When designing a database you want to be looking to normalise the data to prevent redundancy (I\'m sure there is a nice example of this but I don\'t have the url handy maybe someone can help?)

 

It\'s difficult to help without a further understanding of the context.

[gizmola]

I agree with your comment, except for this part:

 

The only time this isn\'t suitable is if each username can have many grades/teachers/etc attached to it.

 

As you stated, if the database is focused on recording data about students, including classes taken, grades given, teachers etc... then this should be reflected in a normalized database structure.

 

Here\'s a quick data model I did illustrating a full featured database model with a few assumptions made, that

 

-Records All classes by subject

-Records a semester (grade period in this model) where class was offered

-Indicates teacher who taught class for semester

-Records students in the class

-Records tests given to class

-Records grade for each student for each test given in class

 

 

This supports any number of teachers, students and classes in combination. It doesn\'t support a class having more than one teacher, but that could be easily added at the cost of a little more complexity.