2 tables or 1?

[rukshilag]

working on a system with 2 tables both have the exact same fields - the tables are past_participant and course_participant - CP table has all current students whilst the PP has only past students.
after a course concluded, that set of students is transferred from CP to the PP table.
is this a good practice? is it better to just have one table with an extra field called type which will define if its a past or current participant?

we are having 2 tables thinking having one might just slow down the search... is this right?

is it better we are having 2 same tables which are the same or just one table with an extra field that defines the type?

[drjohn]

Unless you have many millions of records in the table, one table and a current_student field would work okay. Adding an index or two would speed things up. The primary key will automatically get indexed, but in this case adding an index on current_student would be an excellent idea.

One table may also simplify things when you need data on past and present students in a single query.

(A field called type risks type being a reserved word in your programming language, so current or current_student is probably a better field name.)

[rukshilag]

Unless you have many millions of records in the table, one table and a current_student field would work okay. Adding an index or two would speed things up. The primary key will automatically get indexed, but in this case adding an index on current_student would be an excellent idea.

One table may also simplify things when you need data on past and present students in a single query.

(A field called type risks type being a reserved word in your programming language, so current or current_student is probably a better field name.)

definitely not millions of records - but wat du mean by indexing?

[drjohn]

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

http://www.tizag.com/mysqlTutorial/mysql-index.php

The result is very much faster queries. You set indexes on the fields that you are most likely to use in queries' WHERE clause. Primary keys are automatically created with an index.

Without indexes, MySQL will start at the first record in a table, and read through the entire table to find all of relevant records. If the table has an index, MySQL can find the position of the data in the middle of the data file.

You can add an index to an existing table -
CREATE INDEX employeeid ON myTable (empid);
or if you were creating a new table, you'd add at the end of the create table code eg .....INDEX(empid) }