database.md 3.24 KB
Newer Older
Florent Chehab's avatar
Florent Chehab committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
# Database : description

## Introduction

For this project, a postgres SQL databse has been use.

The full SQL script can be found [here](./data/code/crea_base.sql).


## Comments on the model
Explanation on the tables :

- `Users`

	For this table their is nothing crazy. A `userId` with serial, a `login`, an `email`, a `type` (deducted from the CAS) and a `language` (this attribute is not yet used). `Email` and `type` can be `NULL` to handle group *populating* easily (we accept all logins and create a user for every login that isn't in the db ; this will be done until we can access to the *demeter* infos)


- `Groups`

	Nothing crazy either. If `active` attribute is set to `false`, access deducted from this group won't be granted.

	Two groups are automaticly created, one containing every user, the other for the Admins, if someday we need such a group.
	`preventDeleteMasterAdmins` is here to prevent delation of those groups.


- `UserGroupAssocations`

	Table for storing the association between a user and a group.


- `Forms`

	Table for holding all the data of the forms : `id`, `title`, `infoJson` (not yet in use, maybe if one day there is translation), the `creator`, `formJson` (the form architecture), is the form a `draft`, the `creationDate`, the `lastModificationDate` (not really used), the `closeDate`, are the results public (`publicResult` - not yet used), is the form architecture public (`publicJSONsource`), what kind of information on the user we store (`anonymous` and `personnalInfo`), the `finalResult` (not yet used), `tmp` if the user hasn't changed the title (those form will be deleted on login/logout for db cleaning).

	**Comments on `anonymous` and `personnalInfo`**

	For `anonymous` :
		- if set to `2` : those who answer will be anonymous,
		- if set to `1` : those who answer can choose to be anonymous or not,
		- if set to `0` : those who answer can't be anonymous.

	For `personnalInfo` :
		- if set to `2` : the CAS info of those who answer will be anonimized,
		- if set to `1` : those who answer can choose to share their CAS info or not.
		- if set to `0` : those who answer have to share their CAS info.



- `GroupFormRelations`

	Table holding data on what kind of access is granted to a group for a form. Only `canSeeResults` is used in the app currently : `canAdministrate` is not.


- `FormAccess`

	A quite similar table but this one is dedecated to storing the info on the groups that can answer a form.


- `FormSubmissions`

	A `uid` is attached to each FormSubmission, this string will be used for identifying anonymous user (enabling them to edit the submissions). `results` is a JSON string containing the results as determined by the js saveFormLib. `answers` is a JSON string containing the serialization of the formSubmission for easy repopulation of the form. `userInfo` store the info related to the user info and CAS groups. Those info are stored on creation of the a submission for homogenous form-submission handling (anonymous or not).


- `FormSubmitted`

	A table to know what forms have a user answered : if the user is not anoymous we also store the uid of the form submission.


I am a little bit lazy to comment all the views. But one is really nice : `userFormRightsRecap`. It gives a recap of all the rights a user have *per-form*.