DBMS design for multivalued attributes

Question

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.

Answer 1

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.

Answer 2

You have given very few information, but here is a shot:

create table student 
(
   registration_no    varchar(50)  not null primary key,
   first_name         varchar(50),
   last_name          varchar(50),
   registration_year  integer not null
);

create table printer
(
   printer_id    integer not null primary key,
   printer_name  varchar(50) not null,
   ip_address    varchar(50) not null,
   queue_name    varchar(50) not null
);

create unique index idx_printer_name on printer (printer_name);

create table printer_usage
(
   usage_id         integer not null primary key,
   student_reg_no   integer not null,
   printer_id       integer not null,
   usage_date       datetime not null,
   pages            integer not null
);

alter table printer_usage 
   add constraint fk_usage_student 
   foreign key (student_reg_no) references student (registration_no);

alter table printer_usage 
   add constraint fk_usage_printer 
   foreign key (printer_id) references printer (printer_id);

You will probably need to add more columns to the tables to store all the things you need. I was just guess stuff that you might want to store.