Changes between Version 1 and Version 2 of ERModel


Ignore:
Timestamp:
09/18/26 21:20:07 (12 days ago)
Author:
183164
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ERModel

    v1 v2  
    1010
    1111=== Entities ===
     12
     13==== Admins ====
     14Represents the administrators of the system. Administrators have full control over the application: they manage user accounts, configure the categories of problems and assign reports to municipal workers. They are modeled as a separate entity set because they take part in different relationships (Manages, Assigns) than the other types of users.
     15
     16'''Candidate keys:''' admin_id and email. email is unique for every administrator, but it can change over time, so the generated numeric identifier admin_id is chosen as the primary key because it is stable and shorter.
     17
     18'''Attributes:'''
     19 * admin_id - numeric, required, auto-generated
     20 * full_name - text, required, up to 100 characters
     21 * email - text, required, unique, valid e-mail format
     22 * password - text, required, stored as a hash, never as plain text
     23
     24==== Workers ====
     25Represents the municipal workers who process the reports. Workers receive assignments, update the status of reports and write comments about the progress of the work. They are a separate entity set because only they take part in the Receives, Updates and Writes relationships.
     26
     27'''Candidate keys:''' worker_id and email. worker_id is chosen as the primary key for the same reasons as for Admins.
     28
     29'''Attributes:'''
     30 * worker_id - numeric, required, auto-generated
     31 * full_name - text, required, up to 100 characters
     32 * email - text, required, unique, valid e-mail format
     33 * password - text, required, stored as a hash
     34
     35==== Citizens ====
     36Represents the citizens who use the application to report communal problems. A citizen can submit many reports and follow their status. Citizens are separate from the other users because only they submit reports, and they have an additional optional contact phone.
     37
     38'''Candidate keys:''' citizen_id and email. citizen_id is chosen as the primary key because it is stable, while a citizen may change their e-mail address.
     39
     40'''Attributes:'''
     41 * citizen_id - numeric, required, auto-generated
     42 * full_name - text, required, up to 100 characters
     43 * email - text, required, unique, valid e-mail format
     44 * phone - text, optional, up to 20 characters, digits and the + sign only
     45 * password - text, required, stored as a hash
     46
     47==== Categories ====
     48Represents the categories of communal problems (for example roads, street lighting, waste). Categories are modeled as a separate entity set instead of a text attribute of the report, so that administrators can add and change them, and so that all reports use the same consistent list of categories.
     49
     50'''Candidate keys:''' category_id and name. name is unique, but a category may be renamed, so category_id is chosen as the primary key.
     51
     52'''Attributes:'''
     53 * category_id - numeric, required, auto-generated
     54 * name - text, required, unique, up to 80 characters
    1255
    1356==== Reports ====
    … …  
    2871At least one of location_text or latitude/longitude must be entered.
    2972
    30 ==== Citizens ====
    31 ...
     73==== Photos ====
     74Represents a photo attached to a report as visual documentation of the problem. It is modeled as a separate entity set because one report can have several photos, which cannot be stored in a single attribute of the report.
     75
     76'''Candidate keys:''' photo_id is the only candidate key and it is used as the primary key.
     77
     78'''Attributes:'''
     79 * photo_id - numeric, required, auto-generated
     80 * image_url - text, required, path or URL of the stored image, up to 500 characters
     81
     82==== !StatusLogs ====
     83Represents one change of the status of a report. Every change (submitted, received, in progress, resolved, rejected) is recorded as a new entry, so the full history of a report is kept. This provides the transparency and accountability required by the project, because citizens can see when and how their report was processed.
     84
     85'''Candidate keys:''' log_id is the only candidate key and it is used as the primary key.
     86
     87'''Attributes:'''
     88 * log_id - numeric, required, auto-generated
     89 * status - text, required, the new status: submitted, received, in_progress, resolved or rejected
     90 * changed_at - date and time, required
     91 * note - text, optional, explanation of the change
     92
     93==== Comments ====
     94Represents a comment written by a municipal worker about the progress of solving a report. Comments are a separate entity set because one report can receive many comments over time, each with its own author and time.
     95
     96'''Candidate keys:''' comment_id is the only candidate key and it is used as the primary key.
     97
     98'''Attributes:'''
     99 * comment_id - numeric, required, auto-generated
     100 * content - text, required
     101 * created_at - date and time, required
     102
     103==== Assignments ====
     104Represents the assignment of a report to a municipal worker, made by an administrator. It is modeled as an entity set because the assignment connects three entity sets (Admins, Workers, Reports) and has its own attributes (time and note).
     105
     106'''Candidate keys:''' assignment_id, and the combination of the assigned report and worker, because the same worker cannot be assigned to the same report twice. assignment_id is chosen as the primary key because it is a single simple value.
     107
     108'''Attributes:'''
     109 * assignment_id - numeric, required, auto-generated
     110 * assigned_at - date and time, required
     111 * note - text, optional, instructions from the administrator
    32112
    33113=== Relationships ===
    34114
    35 ==== Submits (Citizens - Reports) ====
    36 Each report is submitted by exactly one citizen, and a citizen can submit many reports. Participation of Reports is total, because a report cannot exist without its author.
     115==== Manages (Admins - Categories) ====
     116Each category is created and managed by exactly one administrator, and an administrator can manage many categories. Participation of Categories is total, because every category must have an administrator responsible for it. Participation of Admins is partial, because an administrator does not have to manage any category.
    37117
    38118==== Classifies (Categories - Reports) ====
    39 ...
     119Each report belongs to exactly one category, and a category can contain many reports. Participation of Reports is total, because a report cannot be submitted without a category. Participation of Categories is partial, because a new category may still have no reports.
     120
     121==== Submits (Citizens - Reports) ====
     122Each report is submitted by exactly one citizen, and a citizen can submit many reports. Participation of Reports is total, because a report cannot exist without its author. Participation of Citizens is partial, because a registered citizen does not have to submit any report.
     123
     124==== Has (Reports - Photos) ====
     125Each photo belongs to exactly one report, and a report can have many photos. Participation of Photos is total, because a photo has no meaning without the report it documents. Participation of Reports is partial, because photos are optional.
     126
     127==== Logs (Reports - !StatusLogs) ====
     128Each status change belongs to exactly one report, and a report can have many status changes over time. Participation of !StatusLogs is total, because a status change always refers to a specific report.
     129
     130==== Updates (Workers - !StatusLogs) ====
     131A status change can be made by one worker, and a worker can make many status changes. Participation is partial on both sides, because some status changes are created automatically by the system (for example the initial "submitted" status), so they have no worker.
     132
     133==== Comments on (Reports - Comments) ====
     134Each comment refers to exactly one report, and a report can have many comments. Participation of Comments is total, because a comment always refers to a report.
     135
     136==== Writes (Workers - Comments) ====
     137Each comment is written by exactly one worker, and a worker can write many comments. Participation of Comments is total, because every comment must have an author.
     138
     139==== Assigns (Admins - Assignments) ====
     140Each assignment is made by exactly one administrator, and an administrator can make many assignments. Participation of Assignments is total, because every assignment must be made by an administrator.
     141
     142==== Assigned via (Reports - Assignments) ====
     143Each assignment refers to exactly one report, and a report can be assigned several times (for example to several workers). Participation of Assignments is total. Participation of Reports is partial, because a new report is not yet assigned.
     144
     145==== Receives (Workers - Assignments) ====
     146Each assignment is given to exactly one worker, and a worker can receive many assignments. Participation of Assignments is total. Participation of Workers is partial, because a worker may currently have no assignments.
    40147
    41148== Entity-Relationship Model History ==
    42149
    43150 * '''v01''' - Initial version of the ER model.
     151
     152== AI Use ==
     153
     154[wiki:ERModelAIUsage ER Model AI Usage]