The Database

In university we have departments (here we call them organizations):

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255)     | NO   |     | NULL    |                |
| description | blob             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

and users:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| upn        | varchar(255)     | NO   | MUL | NULL    |                |
| name       | varchar(255)     | YES  |     | NULL    |                |
| surname    | varchar(255)     | YES  |     | NULL    |                |
| email      | varchar(255)     | YES  |     | NULL    |                |
| updated_at | datetime         | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

The main table is the seminars table of course...

+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id          | int(10) unsigned | YES  | MUL | NULL    |                |
| organization_id  | int(10) unsigned | YES  | MUL | NULL    |                |
| date             | datetime         | YES  |     | NULL    |                |
| duration         | int(3)           | YES  |     | NULL    |                |
| room_id          | int(10) unsigned | YES  | MUL | NULL    |                |
| room_description | text             | YES  |     | NULL    |                |
| speaker          | varchar(250)     | YES  |     | NULL    |                |
| speaker_title    | char(20)         | YES  |     | NULL    |                |
| speaker_bio      | text             | YES  |     | NULL    |                |
| committee        | varchar(200)     | YES  |     | NULL    |                |
| title            | varchar(250)     | YES  |     | NULL    |                |
| abstract         | text             | YES  |     | NULL    |                |
| file             | varchar(200)     | YES  |     | NULL    |                |
| link             | varchar(250)     | YES  |     | NULL    |                |
| link_text        | varchar(255)     | YES  |     | NULL    |                |
| alert_message    | varchar(255)     | YES  |     | NULL    |                |
| alert_deadline   | datetime         | YES  |     | NULL    |                |
| serial_id        | int(10) unsigned | YES  | MUL | NULL    |                |
| cycle_id         | int(10) unsigned | YES  | MUL | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

• duration: minutes
• room_id: referals to the rooms table but can be also a simple string in room_description
• speaker_title: sig/sig.ra/dott/dott.ssa in italian or mr/msr/dott or ....

The seminar takes place in a room which belongs to a place (with address). So we have places table

+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | text             | YES  |     | NULL    |                |
| address | text             | YES  |     | NULL    |                |
| city    | varchar(255)     | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

and rooms table:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| place_id    | int(10) unsigned | YES  | MUL | NULL    |                |
| name        | varchar(255)     | YES  |     | NULL    |                |
| description | text             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

Regarding permission we set an admin table that contains permission for users in particular organization.

Permissions are not about promoting (submitting) new seminars as all authenticatd users can, but are abount permission to

• modify seminars submitted by others
• modify organization properties
• manage repayments
• [...]