Jump to content


Photo

selecting tables, with variables


  • Please log in to reply
3 replies to this topic

#1 Qbasicboy

Qbasicboy
  • Members
  • Pip
  • Newbie
  • 2 posts
  • LocationOhio

Posted 02 January 2004 - 02:41 AM

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
Visit me on the web! http://www.codersunique.com

#2 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 02 January 2004 - 09:56 AM

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.

#3 llama

llama
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationLeicester, UK

Posted 02 January 2004 - 04:31 PM

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.
www.subhex.co.uk
[br]

#4 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 02 January 2004 - 07:39 PM

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.


Posted Image




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users