Database Model

Model of the current OR database:

graphml file to edit the model

Description:

• almost all tables have a technical key (surrogate key), generated by the database on insert and never changed. Its name is ID. In the graphic this column is prefixed with +. If this key is used in relations to other tables (foreign key constraints,abbreviated as FK), its name should always be <table-name>_ID, e.g. USER_ID

• the natural (or business) key (or keys!) of a table are prefixed by * in the graphic. Consider these, to better understand the 'business logic' of openroberta.

• USER has the natual key ACCOUNT

• USER can receive a confirmation email to the provided address: USER_ID is FK in PENDING_EMAIL_CONFIRMATIONS.

• ...

• USER can restore the lost password -- USER_ID is required to define the table

• USER can own multiple robot CONFIGURATIONs -- NAME of the CONFIGURATION, OWNER_ID (USER's) and ROBOT_ID are required to define the table

• USER can own multiple PROGRAMs -- NAME of the PROGRAM, OWNER_ID (USER's), AUTHOR_ID (USER's) and ROBOT_ID are required to define the table

• USER can be an author of multiple PROGRAMs

• Multiple USERs can like multiple PROGRAMs -- USER_ID and PROGRAM_ID are required to define the connection

• Multiple USERs can share multiple PROGRAMs -- USER_ID and PROGRAM_ID are required to define the connection

• Multiple PROGRAMs can be run on a ROBOT -- NAME of the ROBOT is required to define the table

• ROBOT can have multiple CONFIGURATIONs

• Multiple CONFIGURATIONs can be hashed to CONFIGURATION_DATA -- CONFIGURATION_HASH is required to define the table

• TOOLBOX is not used at the moment

Model of the OR database with groups:

graphml file to edit the model

Description (added groups):

• the natural key of a USER is now defined by ACCOUNT and GROUP_ID. If GROUP_ID is 0, then this user is a "normal" user and no group-user.
• if the user owns groups, he/she has role TEACHER and must have an email adress that is confirmed and can be used to restore his/her or the student's lost passwords. Otherwise, the user is a group user (has role STUDENT). By default GROUP_ID is 0 and thus the user is a TEACHER.
• A TEACHER can own a GROUP. The natural keys of a group are NAME and OWNER_ID.
• A STUDENT must belong to a GROUP
• A GROUP can be limited to some ROBOT types. If the list of available ROBOTs is empty, all ROBOTs can be used in the GROUP
• GROUP has a ACCESS_RIGHT_HISTORY that can also be empty. The GROUP_ID iand a timestamp are the natural key of that table.