### Description The data model consists of entities (regions, baskets, stores, ...) that have some mandatory and some optional properties. For example, we should expect a basket to have a description, but the pictures are optional. The database tables should reflect that by using (non-)nullable columns. Mandatory properties should never allow null. Optional properties should use null to denote that the property is not set and should not use empty strings or the number 0. Also, a lot of foreign keys are missing. They should be added even if a cascaded deletion is not necessary. I combed through all tables and made a list of columns that do not fit the expectation of the data model. Some of them are marked with a question mark, which means they need to be discussed. In some cases there are different options (a/b) because I was not sure if a table should be fixed or dropped. I think fixing all these will prevent future problems with inconsistent data. The frontend will then be able to expect consistent data from the API. For each of the following tables: - change it in the database. This might require a database view until the next release. - add comments to the columns if it is not clear what they represent - adjust DTOs - check if the frontend can handle it --- - [ ] fs_bezirk_has_theme: why is this n-to-m relation necessary? Each thread only belongs to one region. It could use a regionId column and be deleted by foreign key. - [ ] fs_content: - `name` is unnecessary because the `id` is the key: remove column - `title`, `body`, `last_mod` can be null: make non-null, `last_mod` should be set on creation - [ ] fs_betrieb_kategorie: - `name`: make non-null - [x] fs_faq, fs_faq_category: drop? - [ ] fs_push_notification_subscription: - `type`: make non-null - [ ] fs_mailchange: - `newmail`, `time`, `token`: make non-null - [ ] fs_event: - `bezirk_id`: make non-null, change foreign-key to `ON DELETE CASCADE` (https://yunity.slack.com/archives/C073WLRRVUY/p1765224414884799) - `name`: make non-null - `bot`: remove - `online`: rename to `type`, make non-null - add foreign key from `foodsaver_id` to `fs_foodsaver.id` - [ ] fs_answer: - `text`: make non-null - `explanation`: make null - `right`: make non-null, rename to `rating` - [ ] fs_question: - `text`: make non-null - [ ] fs_question_has_quiz: a) Quiz questions are not being used in more than one question. The N-to-N mapping is not necessary. Drop the table and move the columns `quiz_id`, `fp` to fs_question? b) - `fp`: make non-null, rename to `error_points` - [ ] fs_quiz: - `name` and `desc`: make non-null - `is_desc_htmlentity_encoded`: make unsigned - `maxfp`: rename to `max_error_points` - [ ] fs_quiz_session: - add foreign key from `quiz_id` to `fs_quiz.id`. What should happen to the sessions if a quiz is deleted? - `status`, `quiz_index`, `quest_count`: make non-null? - `quiz_questions`, `quiz_result`: add comment that the data is set to null by maintenance after the quiz was finished - `fp`: make non-null and unsigned? rename to `error_points` - `maxfp`: make non-null? rename to `max_error_points` - `easymode`: make unsigned, rename to `is_timed` - [ ] fs_region_statistics: - `last_modified`: make non-null default `NOW()` - [ ] fs_region_function: two possibilities a) drop table and add move `function_id` to `fs_bezirk` b) - `target_id` is always the parent region of the working group: remove? - add foreign key from `region_id` (and possibly also `target_id`) to `fs_bezirk.id` - `function_id` (and possibly also `target_id`): make non-null - all columns: make unsigned - [ ] fs_buddy: - `confirmed`: make non-null - [ ] fs_poll_has_options: - `option_text`: make non-null - [ ] fs_poll: - `name`, `description`: make non-null - add foreign key from `author` to `fs_foodsaver.id` - [x] fs_send_email and fs_email_status: drop? - [ ] uploads: - `user`: add comment why it can be null - add foreign key from `user` to `fs_foodsaver.id` - [ ] fs_blog_entry: - `bezirk_id`: remove or use. If use: add foreign key - add foreign key from `foodsaver_id` to `fs_foodsaver.id` - `name`, `teaser`, `body`, `time`: make non-null - `picture`: make null default null - [ ] fs_fetchweight: - `id`, `weight`: make unsigned - [ ] fs_contact: a) drop b) - `email`: make non-null - `name` is always null: remove? - [ ] fs_foodsaver_has_contact: drop if fs_contact is dropped - [ ] fs_email_blacklist: - is read out but never filled - has no UNIQUE column: add a unique key on `email`? - [ ] fs_lebensmittel: - `name`: make non-null - [ ] fs_conversation: - add foreign key from `last_foodsaver_id` to `fs_foodsaver.id` - add foreign key from `last_message_id` to `fs_msg.id` - `last_message_is_htmlentity_encoded` was set to 1 until 2020-05-15, now set to 0: remove and handle in code? Implement time aware message body decoder? - [ ] fs_foodsaver_has_conversation: - `unread`: make non-null - [ ] fs_msg: - `is_htmlentity_encoded`: same as in fs_conversation - add foreign key from `foodsaver_id` to `fs_foodsaver.id` - `time`, `body`: make non-null - [ ] fs_foodsaver_change_history: - Take care with foreign keys because these entries should persist deletions - add foreign key from `fs_id` to `fs_foodsaver.id` - add foreign key from `changer_id` to `fs_foodsaver.id - [ ] fs_store_log: - add foreign key from `store_id` to `fs_betrieb.id` - add foreign key from `fs_id_a` to `fs_foodsaver.id` - add foreign key from `fs_id_p` to `fs_foodsaver.id` - [ ] fs_fetchdate: - add foreign key from `betrieb_id` to `fs_betrieb.id` with ON DELETE CASCADE - `time`, `fetchercount`: make non-null - [ ] fs_abholzeiten: - `betrieb_id`: make non-null - `time`: remove default value - add foreign key from `betrieb_id` to `fs_betrieb.id` - Add comment to `dow`: day of week, Sunday=0 to Saturday=6 - [ ] fs_ipblock: empty and not used anymore. Drop? - [ ] fs_stat_abholmengen: Entries from 2013 to 2016. Not used anymore. Drop? - [ ] fs_verify_history: - `fs_id`, `bot_id`, `change_status`: make non-null - add foreign key from `fs_id` to `fs_foodsaver.id` - add foreign key from `bot_id` to `fs_foodsaver.id` - [ ] fs_chain: - `headquarters_zip`, `headquarters_city`, `headquarters_country`, `forum_thread`: make non-null - [x] fs_kette: unused since 2022. Drop? - [ ] fs_abholer: - `id`: move to front - add foreign key from `foodsaver_id` to `fs_foodsaver.id` - add foreign key from `betrieb_id` to `fs_betrieb.id` - [ ] fs_theme: - add foreign key from `foodsaver_id` to `fs_foodsaver.id` - add foreign key from `last_post_id` to `fs_theme_post.id` - `name`, `time`: make non-null - `name`: some entries are empty strings. Should not happen. - [ ] fs_theme_post: - add foreign key from `foodsaver_id` to `fs_foodsaver.id` - `body`, `time`: make non-null - [ ] fs_post_reaction: - add foreign key from `foodsaver_id` to `fs_foodsaver.id` - `key`: make non-null - [ ] fs_theme_follower: - `infotype`, `bell_notification`: make unsigned - [ ] fs_resource: - `is_private`: make non-null and unsigned - `openness`: make non-null, make tinyint? - [ ] fs_pass_request: - `name`, `time`: make non-null - [ ] fs_pass_gen: - `bot_id` is null for entries before 2016. Remove those and make it non-null? - [ ] fs_bell (fs_bell_old): - `name`, `body`, `icon`, `identifier`: make non-null - `vars`, `link_attributes`: make non-null? - [ ] fs_foodsaver_has_bell: - `seen`: make non-null - [ ] fs_wallpost: - add foreign key from `foodsaver_id` to `fs_foodsaver.id` - `body`, `time`: make non-null - [ ] fs_application_has_wallpost: usage was removed in 2024, drop? - [ ] fs_foodsaver_has_wallpost: - `usercomment`: unused, remove - [ ] fs_question_has_wallpost: - `usercomment`: unused, remove - [ ] fs_usernotes_has_wallpost: - `usercomment`: unused, remove - add foreign key from `usernotes_id` to `fs_foodsaver.id` - [ ] fs_basket: - add foreign key from `foodsaver_id` to `fs_foodsaver.id` - `status`, `time`, `description`, `weight`: make non-null - `fetchtime`, `location_type`, `appost`: unused, remove - `tel`, `handy`: make null, use null as default instead of empty string - `lat`, `lon`: remove standard value - `bezirk_id`: is filled with the user's home region but never used. Remove or use for statistics? If use, add a foreign key. - [ ] fs_basket_anfrage: - `status`: make non-null - `appost`: unused, remove - [ ] fs_basket_has_art, fs_basket_has_types: unused, drop (was discussed in dev call on 24.11.2025) - [ ] fs_mailbox: - `name`: make non-null - `last_access`: written, but never read. Remove? - `member`: unused, remove? - [ ] fs_mailbox_message: - `folder`, `sender`, `subject`, `body`, `time`, `read`, `answer`: make non-null - `body_html`: make null (which it is for outgoing emails, only incoming ones have an HTML body) - [ ] fs_mailbox_member: clear out and drop? - [ ] fs_foodsaver_has_bezirk: a) move `application` to its own table, remove `active` b) - `active`: make non-null - `application`: make null, set to null after application was accepted - [ ] fs_fairteiler: - `bezirk_id`, `name`, `status`, `desc`, `anschrift`, `plz`, `ort`, `add_date`, `add_foodsaver`: make non-null - `lat`, `lon`: make non-null, convert to decimal - `picture`: make null - add foreign key from `add_foodsaver` to `fs_foodsaver.id` - [ ] fs_fairteiler_follower: - `infotype`: set standard value to 0? - [ ] fs_bezirk: - `parent_id`: add comment that it is only null for the root node - `has_children`, `type`, `moderated`: make unsigned - `teaser`, `photo`, `apply_type`: make null (are only non-null for working groups) - `banana_count`, `fetch_count`, `week_num`: make null (are only non-null for working groups), make unsigned - `desc`: unused? - `master`: make null, default null, set all 0 to null - `mailbox_id`: remove standard, fix all that have value 0 - `name`: make non-null - `report_num`, `conversation_id`: unused, remove - `stat_last_update`, `stat_fetchweight`, `stat_fetchcount`, `stat_postcount`, `stat_betriebcount`, `stat_korpcount`, `stat_botcount`, `stat_fscount`, `stat_fairteilercount`: make default 0 - remove duplicate foreign key from `parent_id` - add foreign key from `master` to `fs_bezirk.id` - add foreign key from `mailbox_id` to `fs_mailbox.id`. Related to this: mailboxes are not deleted if a region is deleted. This can't be done with a foreign key but should be done in the backend code. - [ ] fs_bezirk_closure: - remove duplicate foreign keys from `bezirk_id` and `ancestor_id` - [ ] fs_betrieb: - add foreign key from `bezirk_id` to `fs_bezirk.id` - add foreign key from `kette_id` to `fs_chain.id` - add foreign key from `team_conversation_id` to `fs_conversation.id` - add foreign key from `springer_conversation_id` to `fs_conversation.id` - `lat`, `lon`: convert to float, make non-null - `name`, `str`, `team_conversation_id`, `springer_conversation_id`: make non-null - `status_date`: make non-null, rename to `updated_at` - `betrieb_status_id`: rename to `cooperation_status` - `status`: unused, remove - `ansprechpartner`, `telefon`: rename - `fax`: is often used for other information. Rename to `internal_information` or `store_manager_information`? - `begin`, `besonderheiten`: rename - `public_time`: make unsigned - `ueberzeugungsarbeit`, `presse`, `sticker`, `abholmenge`: rename, make unsigned - `team_status`: make unsigned - `prefetchtime`: make null, set 0 to null - `use_region_pickup_rule`: convert to tinyint, make unsigned - `springer_conversation_id`: rename - [ ] fs_betrieb_has_lebensmittel: a) drop b) add foreign key from `betrieb_id` to `fs_betrieb.id` - [ ] fs_betrieb_notiz: drop and remove triggers that synchronise it with fs_wallpost? - [x] fs_betrieb_status: unused, drop - [ ] fs_betrieb_team: - [x] add foreign key from `betrieb_id` to `fs_betrieb.id` - [x] add foreign key from `foodsaver_id` to `fs_foodsaver.id` - [ ] `verantwortlich`, `stat_add_date`: make non-null - [ ] `active`: convert to tinyint, make unsigned - [ ] fs_location: only used for events. Multiple events per location only exist for old events. a) drop and move the columns to fs_event? b) - `name`, `lat`, `lon`, `city`, `street`: make non-null - `zip`: make non-null, rename to `postal_code` - [ ] fs_rating: - [x] `rating`, `ratingtype`: unused, remove - [ ] remove the obsolete comment from the table - [ ] fs_report: - `reporter_id`, `reporttype`, `time`, `msg`: make non-null - `betrieb_id`: convert 0 to null, set null as default - `committed`: unused?, remove? - `tvalue`: rename? make non-null - `report_reason_id`: make non-null, make unsigned - add foreign key from `foodsaver_id` to `fs_foodsaver.id` - add foreign key from `reported_id` to `fs_foodsaver.id` - add foreign key from `betrieb_id` to `fs_betrieb.id` - [ ] fs_foodsaver: - [ ] `bezirk_id`: make null (e.g. for foodsharers) - [ ] `position`, `about_me_public`: make null default null - [ ] add foreign key from `bezirk_id` to `fs_bezirk.id`. This requires that it is set to null when the account is deleted. - [ ] add foreign key from `mailbox_id` to `fs_mailbox.id`. This would require that it is set to null when the account is deleted. Do we want that? If so, the mailbox can be deleted too. - [ ] `rolle`: make unsigned - [ ] `geschlecht`: make null, set 0 to null, remove 0 as option - [x] `orgateam`, `new_bezirk`, `want_new`, `type`, `beta`, `data`, `admin`, `contact_public`, `orgateam`: unused, remove (already removed in dev) - [ ] `newsletter`: remove (when introducing synchronisation with Listmonk) - [ ] `token`: make null (when introducing the mandatory email verification) - [ ] `stat_postcount`: make unsigned - [ ] `stat_buddycount`: make default 0 - [ ] `stat_fetchrate`: unused, remove? - [ ] `option`: replaced by fs_foodsaver_has_option, remove - [ ] Add comment to deleted_at: deletion day of account, null if account is active - [ ] fs_apitoken: - make the key on `foodsaver_id` unique to allow only one token per user? If so, clean up duplicate keys per user later. `SELECT foodsaver_id, COUNT(token) FROM fs_apitoken GROUP BY foodsaver_id HAVING COUNT(token) > 1`