vendredi 1 avril 2016

Database for available classrooms at the current time

I want to create a database for a mobile app, the app displays to the user (student) all the available (free/dispo) classrooms at the current time.

For example: a student open the app Monday at 11:00, the app will show classroom 303 as available.

ClassroomLib: 301

   |08:00-09:30|09:31-11:10|11:11-12:50|12:51-14:30|14:31-16:10|16:11-17:50|
SAT|  available|           |           |           |available  |available  |
SUN|           |           |           |available  |           |available  |
MON|           |           |available  |           |           |available  |
TUE|           |           |           |available  |           |           |
WED|           |available  |           |           |           |           |
THU|           |           |           |           |available  |available  |

ClassroomLib: 303

   |08:00-09:30|09:31-11:10|11:11-12:50|12:51-14:30|14:31-16:10|16:11-17:50|
SAT|           |available  |           |           |available  |available  |
SUN|           |           |           |           |           |           |
MON|           |available  |           |           |available  |           |
TUE|           |           |           |           |           |           |
WED|           |           |available  |           |available  |available  |
THU|           |           |           |           |available  |available  |

I designed the db as follows:

CREATE TABLE classrooms(
classroomId INTEGER PRIMARY KEY AUTOINCREMENT,
classroomLib VARCHAR(50) NOT NULL,
);
CREATE TABLE schedules(
dayWeek INTEGER PRIMARY KEY,
hStart TEXT PRIMARY KEY,
hEnd TEXT PRIMARY KEY,
);
CREATE TABLE dispos(
classroomId INTEGER PRIMARY KEY,
dayWeek INTEGER PRIMARY KEY,
hStart TEXT PRIMARY KEY,
hEnd TEXT PRIMARY KEY,
classroomDispo INTEGER, #boolean
);

I got a lot of classrooms, and for each classroom i got 36 entries, is there a better architecture ?

Aucun commentaire:

Enregistrer un commentaire