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.

score 1 · Accepted Answer · edited Jun 17 '14 at 13:12

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.

edited Jun 17 '14 at 13:12

BenMorel

34,448
50
182
322

answered Aug 28 '12 at 18:42

djadmin

1,742
3
19
27

Actually my concern is that nearly 10MB of data may be generated each day for the usage. I should store the document name, time and number of pages mainly from the log. I wanted an efficient way to do this as this should be queried to calculate dues accordingly. And also data needs to be retrieved using php front end. – user1631171 Aug 29 '12 at 14:03
Yeah ! This method will solve your problem since 10MB Data each day is not a big issue. And while retrieving you can optimize it by querying in an efficient manner using some searching algorithms and then querying the database ! – djadmin Aug 29 '12 at 16:53

score 0 · Answer 2 · answered Aug 28 '12 at 18:48

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.

My question is not about syntax of the sql or how to create a table. Huge amount of data will be generated each day like around 10MB and this needs to be stored and later used to calculate dues for students. I want to discuss about an efficient way to do this. Querying should be faster. Is it appropriate to go for mysql or may be database like mongodb (but requirement is to use mysql. i dont know if mysql would be able to support such huge transactions. I tried to import a days log into mysql in xampp and it gave out of memory). Please suggest some useful tips and information. — user1631171, Aug 29 '12 at 14:07
@user1631171: 10MB of data each day does not remotely qualify as "*huge*". — , Aug 29 '12 at 16:12

DBMS design for multivalued attributes

2 Answers2