Build data models for Sales/Transfers

[andrea-williams]

Describe the task

Create database migration with the models needed for the Sales & Transfers epic for Registration Part 2.

UPDATE Aug 14: we've chosen to switch to the Polymorphic Models approach, thus abandoning our earlier decision to store data as json.

Blocked - #1616 Blocking - #1709

Acceptance Criteria

• [x] our existing Event model will be used as the abstract "parent" model for all event types. Remove the additional_data and type columns from this model (and from the relevant fixtures). Replace the facility_id column with a many-to-many relationship of facilities. Event model will have the standard audit fields and effective_date (required, date), operation_id (optional, only populated if the event is at the operation-level), and status

• [x] create new model Restart(Event)

• [x] create new model Closure(Event) with optional string field for description

• [x] create new model TemporaryShutdown(Event) with optional string field for description

• [x] create new model Transfer(Event) with fields:

  • description (required, freeform text field)
  • future_designated_operator (required, with Enum of {MY_OPERATOR, OTHER_OPERATOR, NOT_SURE})
  • other_operator (required, FK to Operator table)
  • other_operator_contact (required, FK to Contact table via operator_contacts table using id from other_operator field)
  • status (required, with Enums of {COMPLETE, PENDING, TRANSFERRED}, default value 'PENDING')

• [x] models included in tests (test_<model_name>.py)

• [x] add/edit fixtures for mock data for each of the affected models

Additional context

• List of fields by event (finalized) in this doc: https://bcgov.sharepoint.com/:w:/r/teams/00608-CPRInternal/Shared%20Documents/Carbon%20Pricing%20Transition%20SMEs/Reportable%20events.docx?d=wdbfad4f70dd749a89fd91b29f949f2c8&csf=1&web=1&e=iee8zs

[patrickisaac]

Notes:

• Setting up meeting with Business Area

[patrickisaac]

Andrea will be creating an additional ticket related to this work.

[andrea-williams]

Ticket to be updated following dev discussion tomorrow

[Sepehr-Sobhani]

My thoughts:

Using Nullable Fields

Pros:

• Structured Data: Each type of event can have dedicated columns in the database table, allowing for a well-defined schema. This makes the data easier to query, index, and understand.

• Validation: Django models provide built-in validation for each field, which can be enhanced with custom validation methods using Pydantic. This ensures data integrity at the model level.

• Ease of Use: When querying the database, you don’t need to parse a JSON field to access specific pieces of information; everything is already well-structured and accessible. Cons:

• Scalability: As the number of event types and associated data fields grows, the number of nullable columns can increase, leading to a bloated and less normalized table structure.

• Maintenance: Adding new event types may require schema migrations, which can be cumbersome and increase the risk of errors. It also requires careful planning to avoid introducing too many nullable fields.

• Data Integrity: Even though you can use validation to enforce rules, having many nullable fields can lead to data sparsity, making it harder to ensure that the right fields are filled for each event type.

Using a JSON Field

Pros:

• Flexibility: A JSON field can store arbitrary data structures, allowing you to easily add new event types with different data requirements without altering the database schema.
• Scalability: Since new data types can be added without changing the schema, this approach is highly scalable, especially when the data varies significantly between event types.
• Schema Evolution: You can evolve the schema for different event types more rapidly, as changes to what data is stored can be made at the application level without needing database migrations.

Cons:

• Lack of Structure: JSON fields are not as well-structured as relational fields, which can make it harder to enforce data integrity and consistency. This also makes querying specific pieces of data more complex.
• Validation: With a JSON field, you lose some of the automatic validation that Django provides for its standard model fields. You can implement validation with Pydantic or custom logic, but it adds complexity.

Polymorphic Models

Each event type would have its own model inheriting from a base Event model. This allows each event type to have its own fields but still be treated as a type of Event. Pros:

• Strong typing and validation per event type.
• Clear separation of data.

Cons:

• Requires more effort to maintain.(Adding new event types can lead to having more data models)