a SQL-(mysql) question about which method is better

I need to put something in my database so i can check on students which courses they have, but which method is better

{

Table1
Rows: 
    studentID,
    lessonID,
    hasPerfectCourse,
    courseID,

}

or

{

Table1
Rows: 
    studentID,
    lessonID 

Table2
Rows: 
    studentID,
    hasPerfectCourse,
    courseID

}

and they both do the same thing, but I would like to know which one is better for performance

EDIT so if the student passed all his exams he'll have a perfectcourse and will just get a courseid so it's possible to get all the lessons with that courseid and if the student doesn't have a perfectcourse, for examp开发者_开发知识库le he/she still have lessons from the first year while he/she is in the third year with also some lessons from the second year hope I made myself understandable because i'm not the best person to explain something :D /EDIT Greetz, Haroun

The concept of less tables => less joins => better performance leads to the "god table" anti-pattern.

My opinion is that you aim for 5th normal form in design, and then de-normalise where appropriate based on peformance and architectural considerations.

In short, does holding the data in one table ever lead to redundant duplication of data across rows (data, not keys), or combine properties of disparate/discrete entities into a single table?

If you're really concerned about "getting it right", read up on the web about Normalisation :)

I think you're asking how to design the tables, is that correct?

If so, the right thing to do is to normalize the database. The key idea behind normalization is to avoid having copies of non-key data spread around the database. To that end, I would suggest the most normalized thing to do is probably to have this set of tables:

CREATE TABLE students (id, ...);
CREATE TABLE courses  (id, ...);
CREATE TABLE schedules (student_id, course_id);

The third table there is frequently called a join table and is used to express a many-to-many relationship. To query the courses a given student is taking, you would ask:

SELECT * FROM schedules WHERE schedules.student_id = :student_id;

To query the students signed up for a given course, you would ask:

SELECT * FROM schedules WHERE schedules.course_id = :course_id;

In contrast to fdaines, I would suggest that more tables => more normalization => fewer data anomalies. I would also say that joins are not intrinsically expensive, especially if you have the right indices; there are better ways to improve performance without destroying your data integrity or making your database difficult to query.

Typically a narrower table will perform better. Also, you should be worrying about storing your data in a normalized way. Here is an article that will help you with problem http://www.edbarlow.com/document/optimize.htm

You will first have to figure out the dependencies of all the variables you have. According to that you then create a new db design. Such as the course_name is dependent on course_id, and student_name on student_id. So instead of putting all the 4 variables in same table, you separate them. This reduces the redundancies between tuples and also becomes easy to handle the null values. This is fifth normal form. And it is always easy to fire queries on these kind of tables.

It is also helpful when you have to do Indexing on the table. Because variables having null values can't be indexed upon.

I think that in most of the cases you have to think:

Less tables => less joins statements => better performance