I have to design a database where some information about usage of printer resource is to be recorded in a mysql database. What is the best way to design the database? I do not want to create a table for each student as there would be around 6000 tables and which would keep growing each year if archives are to be maintained. Also it is difficult to create tables based on registration number of student. Is there a better way than storing multivalued attribute for details of printing. Please suggest some solutions. Also querying should be effective.
There is no need to create different tables for each student. Just create a Table STUDENT which will contain the personal details of the student identified by their Registration number (lets say Regno-PrimaryKey).
And then another Table RESOURCE, which will have the following schema: -RecNo Integer PK -StudentID Foriegn key referenced to Regno in the Student Table -Usage or Data,Time(if you require)
This will work for and you need not have to create 6000 or more tables.