# Database Schema

**Analysis Date:** 2026-04-27

## Overview

MySQL database on mysql7.ceti.pl. Custom DAL layer; no ORM. All migrations are manual SQL files with idempotent PHP runners in `_rejestracja/sql/`.

## Core Tables

### `mf_participant`

Primary registration table. One row per conference registrant.

| Column | Type | Notes |
|--------|------|-------|
| `id_mf_participant` | INT PK AUTO_INCREMENT | Primary key |
| `name` | VARCHAR | First name |
| `surname` | VARCHAR | Last name |
| `degree` | VARCHAR | Academic title/degree |
| `position` | VARCHAR | Job position |
| `phone` | VARCHAR | Phone number |
| `fax` | VARCHAR | Fax number |
| `email` | VARCHAR | Email address |
| `institution` | VARCHAR | Institution name |
| `address` | VARCHAR | Street address |
| `post_code` | VARCHAR | Postal code |
| `city` | VARCHAR | City |
| `nip` | VARCHAR | Tax ID (NIP) |
| `additional_info` | TEXT NULL | Free-text notes — added 2026-04-27 |
| `referat` | TINYINT | Presentation flag (0/1/2) |
| `poster` | TINYINT | Poster flag (0/1/2) |
| `message` | TEXT | Presentation topic |
| `autor` | VARCHAR | Co-authors |
| `participation_option` | VARCHAR | `full` / `one_day_lodging` / `one_day_no_lodging` |
| `participation_days` | VARCHAR | Selected days (for one-day option) |
| `diet` | TINYINT | Dietary preference (1=standard, 2=special) |
| `diet_special` | VARCHAR | Special diet description |
| `fee_full` | TEXT | Serialized fee selection array |
| `fee_one_day` | VARCHAR | One-day fee value |
| `price` | DECIMAL | Calculated total price |
| `agree1` | TINYINT | Data processing consent |
| `agree2` | TINYINT | Image consent |
| `status` | TINYINT | Payment status (1=no, 2=yes) |
| `location` | TINYINT | Conference location (1=aktualia, 2=PAN) |
| `date_add` | DATETIME | Registration timestamp |

**Model:** `_rejestracja/core/model/MfParticipant.class.php`
**DAL:** `_rejestracja/core/model/MfParticipantDAL.class.php`

### `mf_parameters`

Pricing/fee configuration. Each row is a fee option (accommodation, conference fee, etc.).

| Column | Type | Notes |
|--------|------|-------|
| `id_mf_parameters` | INT PK | Primary key |
| `name` | VARCHAR | Display name |
| `opis` | TEXT | Description |
| `price` | DECIMAL | Regular price (net) |
| `price_prom` | DECIMAL | Promotional/discount price |
| `price_progres` | TINYINT | Progressive pricing flag |
| `count_progres` | INT | Progressive pricing count threshold |
| `unit` | VARCHAR | Unit label |
| `link_id` | INT | Grouping link ID |
| `type` | TINYINT | Parameter type category |
| `sort` | INT | Display order |
| `publication` | TINYINT | Published/active flag |

**Model:** `_rejestracja/core/model/MfParameters.class.php`
**DAL:** `_rejestracja/core/model/MfParametersDAL.class.php`

### `mf_dictionary`

Key-value store for all translatable/editable text strings visible in the UI.

| Column | Type | Notes |
|--------|------|-------|
| `id_mf_dictionary` | INT PK | Primary key |
| `keyword` | VARCHAR | Lookup key (e.g., `registration_yes`) |
| `value` | TEXT | Display value |
| `location` | TINYINT | Site location (1/2) |

**Model:** `_rejestracja/core/model/MfDictionary.class.php`
**DAL:** `_rejestracja/core/model/MfDictionaryDAL.class.php`
**Usage:** `{translate word='key'}` Smarty plugin → `MfDictionaryDAL` lookup

### Supporting Tables

- `wp_setup` — scalar/list settings for the registration form (Phase 3 form settings)
- `mf_article` / `mf_article_content` — content management (HomeSite admin)
- Additional tables inferred from DAL files in `core/model/`

## Migration Pattern

**SQL file:** `_rejestracja/sql/YYYY-MM-DD-description.sql`
**Runner:** `_rejestracja/sql/apply-YYYY-MM-DD-description.php`

Runner pattern:
1. Check `INFORMATION_SCHEMA` (or similar) before altering
2. Requires `?run=YYYYMMDD` query param (or `--run` CLI flag) to execute
3. Prints "already exists / skipped" or "success" output
4. Web-accessible — see CONCERNS.md for security note

**Applied migrations:**
- `2026-04-24-registration-form-settings` — `wp_setup` table (Phase 3, Plan 01)
- `2026-04-27-additional-info-field` — `additional_info` column on `mf_participant` (Phase 3, Plan 02)

## DAL Query Pattern

```php
$dalData = MfParticipantDAL::GetDalDataObj();
$dalData->addCondition('location', 1);
$dalData->setSortBy('id_mf_participant DESC');  // value injected directly into ORDER BY
$results = MfParticipantDAL::GetResult($dalData);
```

`setSortBy()` value is injected verbatim into `ORDER BY` — include `DESC` in the string.

---

*Schema analysis: 2026-04-27*
*Update when tables or columns change*