| Index: trunk/extensions/EducationProgram/docs/schema.txt |
| — | — | @@ -0,0 +1,72 @@ |
| | 2 | +This page describes the database schema used by the Education Program extension |
| | 3 | +from a high level perspective. Documentation on individual fields can be found |
| | 4 | +in the schema file at sql/EducationProgram.sql. |
| | 5 | + |
| | 6 | +An online version of this document can be found at |
| | 7 | +https://www.mediawiki.org/wiki/Extension:Education_Program/schema |
| | 8 | + |
| | 9 | +== Relational, denormalized and revision storage == |
| | 10 | + |
| | 11 | +The extension keeps up to 3 copies of the same data, each optimized |
| | 12 | +for a particular purpose. |
| | 13 | + |
| | 14 | +* Relational storage is the data in it's canonical form. All other copies of the |
| | 15 | + data are computed from this. This data can be found in some of the fields of |
| | 16 | + the main tables. |
| | 17 | + |
| | 18 | +* Denormalized storage is the data in a format optimized for doing queries against. |
| | 19 | + It is always computed from the relational storage data. This data can be found |
| | 20 | + in some of the fields of the main tables. |
| | 21 | + |
| | 22 | +* Revision storage is the ep_revisions table which contains blobs with the |
| | 23 | + relational data for one particular object. |
| | 24 | + |
| | 25 | +The extension uses the DBDataObject and DBTable classes for virtually all |
| | 26 | +it's database interaction. These have mechanisms for distinguishing between |
| | 27 | +relational and denormalized data (referred to as "summary data" in their docs). |
| | 28 | +All revisioning work is done through EPRevision and EPRevisions using EPRevisionedObject. |
| | 29 | + |
| | 30 | +== Where to find the stuff == |
| | 31 | + |
| | 32 | +=== Institutions === |
| | 33 | + |
| | 34 | +Institutions are stored in the ep_orgs table. You can get the linked courses |
| | 35 | +via the org_courses field, which is an array with course IDs and is a denormalized field. |
| | 36 | + |
| | 37 | +=== Courses === |
| | 38 | + |
| | 39 | +Courses are stored in ep_courses. They are linked to their institution via |
| | 40 | +the course_org_id field, which is a foreign key on ep_orgs.org_id. |
| | 41 | + |
| | 42 | +You can find linked users (students, instructors, ambassadors) via the |
| | 43 | +ep_users_per_course table. This table contains the users id, the course id |
| | 44 | +and an integer indicating the role the user has. |
| | 45 | + |
| | 46 | +=== Articles === |
| | 47 | + |
| | 48 | +Students can associate themselves with articles they are working on. |
| | 49 | +These are stored in the ep_articles table. It contains a course id, |
| | 50 | +a user id and a page id (foreign key on page.page_id), which can be |
| | 51 | +used to get all articles worked on by a course, a student, or a |
| | 52 | +combination of both. |
| | 53 | + |
| | 54 | +=== Students === |
| | 55 | + |
| | 56 | +Students are stored in the ep_students table. The students table |
| | 57 | +can be through of as an "extension" to the user table. It has |
| | 58 | +a user_id field, which is a foreign id on user.user_id and |
| | 59 | +several other fields with student data. This is mostly denormalized |
| | 60 | +data though, so this tables primary use is getting lists of |
| | 61 | +students (or linked data) in a computational sane fashion. |
| | 62 | + |
| | 63 | +=== Ambassadors and instructors === |
| | 64 | + |
| | 65 | +Like students, each of these roles have their own tables, |
| | 66 | +which can be thought of as extensions to the user table: |
| | 67 | + |
| | 68 | +* ep_instructors |
| | 69 | +* ep_oas (Online Ambassdaors) |
| | 70 | +* ep_cas (Campus Ambassdaors) |
| | 71 | + |
| | 72 | +The ambassador tables contain ambassador profile info. |
| | 73 | +The instructor table is currently not used. |