The current database schema has six problems:
* **German column names** - This leads to confusion for non-German speaking developers, diminishing developer experience, making onboarding more involved and leading to subtle errors: `adresstyp` vs `address_type`, note the different amount of "d"
* **Large Object (LOB) Columns** (`data` fields) - This makes it hard to export and query existing data.
* ~~**Persistence layer breaks the boundaries of bounded contexts**. Example: Both Donations and Memberships have a relation to Address Changes, but Address Change is its own bounded context and the Donation and Membership Entities (Business Objects) don't have references to Address Change. The reference is only there as a convenience, to trigger the creation of an AddressChange record when we save Donations/Memberships. Here, the framework/storage layer bleeds into the BC.~~
* **Overloaded Status** - For donations, the payment status (waiting for payment, received payment notification from external provider), donation status (new, deleted in Fundraising Operation Center, Canceled on the Confirmation Page) and moderation status (Address contains suspicious words, donation comment contains suspicious words) and kept in one field, leading to data loss on status changes because the status has no "permutations" for the three different aspects. For memberships the situation is slightly better: Status is kept as flags in bit field on the database level, but that makes it hard to comprehend & the implementation.
* **Inefficient Export status** - We use nullable date fields as indicators for a donation being exported. This makes the index bloated (we'll never query for specific dates) and slow.
* **Too many nullable values** - using NULL instead of empty strings for optional or anonymizable values leads to unneccessary null checks in properly typed PHP (e.g. `fullName`)
During this refactoring, these problems should be rectified, with the following **constraints**:
* The FundraisingFrontend, Fundraising Operation Center and Export (SpendenDumper) must be operational at all times.
* ~~To enforce the separation of the bounded contexts, we want to get rid of FundraisingStore "library" dependency in the fundraising-donation, fundraising-memberships and fundraising-subscriptions code repositories. Each bounded context should manage its own database schema.~~
* Entities in different bounded contexts are not allowed to reference each other (creating a dependency on entities outside the bounded context). If there is a reference on the storage level, for example to aggregate data for export, the reference should be inserted/changed at the storage level and should not show up in the entity definition.
* We want to keep the code of our domain objects unaffected by database technology - no ORM annotations on them. Use a separate XML file instead
**Acceptance Criteria**:
* All column names are in English
* If using ENUM or ENUM-Like columns (e.g. `address_type`), the values are in English.
* The data stored in the LOB is split out into tables
* The data stored in the "status" field is split among the entities, with a separate database field for each purpose.
* The export flags are booleans.
* All database access is behind one or more interfaces, using the [[ https://martinfowler.com/eaaCatalog/repository.html | Repository Pattern ]]
* We're still able to do database migrations across all bounded contexts.
* String and integer values are non-nullable and have empty strings or zero as defaults.
* We have a clear model of the various states the data can be in, with optional relationships depending on the state:
- Unconfirmed donations with extrenal providers might have no payment data
- Anonymized donations don't have donor data
- Anonymized membership applications don't have applicant data
- etc
* The unit tests prove that we can transparently load and update data in the various states, without losing/accidentally creating optional data
**Note**:
* To keep the Fundraising Operation Center and export working without huge refactorings, we could store the data redundantly, using the [[ https://www.martinfowler.com/bliki/StranglerApplication.html | StranglerApplication ]] pattern:
1) Copy the `DoctrineDonationRepository` and `DoctrineMembershipApplicationRepository` to the FundraisingFrontend, change the `Doctrine` prefix to `Legacy`
2) Change the `DoctrineDonationRepository` in `fundraising-donation` and `fundraising-membership` to map the Domain entities to database tables.
3) In FundraisingFrontend, create `DonationRepositoryWrapper` and `MembershipApplicationRepositoryWrapper` classes that implement the repository interfaces and have the Legacy and newly created repositories as dependencies. Whenever an interface method is called, both repositories must receive the method call. There must be a "mapping table" that maps between the automatically generated IDs for the legacy and "new" entities.
**Possible tables/entities (to be discussed)**
* donation
* With a better state, separating payment state, soft delete, export, etc. See for example {T137704}. Payment state should move into payment domain.
* Maybe the concept of a "followup donation" (for recurring payments) that's linked to a previous donation and shares its entities. But maybe that's too much and should be implemented as a detail of the payment domain.
* donor (mostly differentiated by different address types, so far it's "private" and "company", but could become "email" in the future, see {T220367})
* payment (we'll have to see how we model the different payment types & their metadata), see https://github.com/wmde/FundraisingFrontend/blob/master/doc/Planning_for_Payment_refactoring.md
* comment
* tracking (impcount, keyword and campaign from banner). See {T134327} for deprecated columns
* buckets (bucket and name campaign from banner)
* membership_applications
* membership_applicants
* subscriptions
* address_change
* Change Log (Which user in the Fundraising Operations Center triggered which status changes, payment notifications, cancellations by user, exports). Entries can come from all Applications.
* Users (for FOC)
* Impression counting (should be its own domain/entities in the backend, depending on the outcome of {T243092}.
We don't need a 1:1 mapping between Domain object and tables. Objects (like `DonorName`) could be implemented as Doctrine [[ https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/cookbook/custom-mapping-types.html | Mapping Types ]].
Resources:
* https://www.martinfowler.com/articles/evodb.html
* https://martinfowler.com/eaaCatalog/repository.html