# 90 — Master ERD Analysis (OB/GYN Legacy Database)

Source: `_db/obgy_12-7-2024.sql` — **312 tables, zero declared foreign keys**. All relationships are implicit, inferred from column naming conventions and verified against JOIN statements in `core/controllers/*.php`.

## 1. Hub Entity Map

| Hub | PK | Referenced by | Via columns |
|---|---|---|---|
| `patients` | `id` | **73 tables** | `patientid`, `patient_id` |
| `awusers` | `user_id` (not `id`!) | **59 tables** | `doctorid` (~45 clinical tables), `userid`/`user_id`, `memberid` (board module), `assistantid`/`assistant2id`/`anasthetstsid`/`anasthetsts2id` (surgery team), `enddoctorid`, `sender_id`/`receiver_id`, `userid_edit`, `t1_userid`/`t2_userid` |
| `visits` | `id` | only `visits_updates` (audit) | `visitid` |
| `branches` | `id` | 2 tables | `awusers.branch_id`, `visits.branch_id` |
| `infertilitysheet` | `id` | **17 tables** | `infertilitysheetid` |
| `ancsheet` | `id` | 6 tables | `ancsheetid` |
| `mointoringsheet` | `id` | 3 child tables | `mointoringsheetid` |
| `drugs` | `id` | 13 tables | `drugid`, `drug_id`, `drugs_id` |
| `invests` | `id` | 7 tables | `investid` |

**Critical architectural finding:** clinical tables never reference `visits` — there is no `visitid` column anywhere except `visits`/`visits_updates`. All clinical data links to the patient via `patientid` + a date column. It is therefore impossible to attribute a clinical record to a specific reception visit.

## 2. Full Relationship List (child.column → parent.column)

### patients hub (73 referencing tables)
`visits.patientid`, `old_visits.patientid`, `visits_updates.patientid`, `patients_updates.patientid`, `endvisitreports.patientid`, `lastvisit.patientid`, `records.patientid`, `patientfiles.patientid`, `instruction.patientid`, `excelinfopatients.patientid`, `device_tracking.patient_id`, `recepittmp.patient_id` → `patients.id`

Clinical sheets: `ancsheet`, `gynasheet`, `infertilitysheet`, `ivfsheet`, `mointoringsheet`, `followupcard`, `followup`, `mainantenental`, `maingyna`, `gyna`, `infertility`, `gynainfertility`, `gynainfertilityplan`, `antenalvisit`, `examination` (all `.patientid` → `patients.id`)

History (`ph*`): `phobstetric`, `phmenstrual`, `phfamily`, `phcontraception`, `phpastmedical`, `phpastsurgical`, `phpastgynecological`, `phpastart`, `phpasticsi`, `phobstericterplace`, `previous_marriage`, `hus_previous_marriage` (`.patientid` → `patients.id`)

Imaging/procedures: `ultrasound`, `ultrasoundgyna`, `ultrasoundobst`, `gynaus`, `ustv`, `sonar`, `gtimage`, `hsg`, `hysteroscopy`, `laparoscopy`, `mrict`, `pathology`, `semen`, `hormon`, `operativedetails`, `op_wait_list`, `op_4d_list` (`.patientid` → `patients.id`)

Drugs/invest children (also carry `patientid`): `ancsheetdrugs`, `ancsheetinvest`, `gynadrugs`, `gynainvestigation`, `gynasheetdrugs`, `gynasheetinvest`, `infertilitydrugs`, `infertilitysheetdrugs`, `infertilitysheetinvest`, `mainantenentaldrugs`, `mainantenentalinvest`, `mainantenentalus`, `mointoringsheetdrugs`, `mointoringsheetinvestigation`, `otherinvestigationsrows`

Finance: `totalbalance.patientid`, `totalbalancepaids.patientid` → `patients.id`

### visits
- `visits.patientid` → `patients.id` *(verified: `JOIN patients ON visits.patientid = patients.id` in controllers)*
- `visits.detectionid` → `detections.id` *(verified: `LEFT JOIN detections ON visits.detectionid = detections.id`)*
- `visits.visit_period` → `visit_periods.id`
- `visits.user_id`, `visits.enddoctorid` → `awusers.user_id`
- `visits.branch_id` → `branches.id`
- `visits_updates.visitid` → `visits.id` (audit copy)

### Sheet parents → children
- `ancsheetdrugs.ancsheetid`, `ancsheetinvest.ancsheetid`, `ancnewvisit.ancsheetid`, `investigations.ancsheetid`, `registeration.ancsheetid`, `op_4d_list.ancsheetid` → `ancsheet.id`
- `newvisitg.gynasheetid` → `gynasheet.id`
- `infertilitysheetid` → `infertilitysheet.id` from 17 tables: `newvisit`, `tvs`, `dtvs`, `sis`, `folliculom`, `hormonalprofile`, `hormonalprofile2`, `semen2`, `semeninfertility`, `hsginfertility`, `hysteroscopyinfertility`, `laparoscopyinfertility`, `icsi`, `operations`, `wifep`, `awifep`, `wifeepc`
- `eprep.ivfsheetid`, `ovst.ivfsheetid` → `ivfsheet.id`
- `mointoringsheetdrugs.mointoringsheetid`, `mointoringsheetinvestigation.mointoringsheetid`, `mointoringsheetvisits.mointoringsheetid` → `mointoringsheet.id`
- `followupcarddrugs.cardid` → `followupcard.id`
- `followupvisit.followid`, `followupinvest.followid`, `followupexam.followid`, `followupdiagnosis.followid` → `followup.id`
- **Anomaly:** `gynasheetdrugs`, `gynasheetinvest`, `infertilitysheetdrugs`, `infertilitysheetinvest` have **no sheetid column** — they link via `patientid` only (de-facto one sheet per patient).

### Catalogs
- `{ancsheetdrugs, gynadrugs, gynasheetdrugs, infertilitydrugs, infertilitysheetdrugs, mainantenentaldrugs, mointoringsheetdrugs, followupcarddrugs, operativedetailsdrugs}.drugid` → `drugs.id`
- `pharmacystore.drug_id`, `receiptdrugs.drugs_id`, `importdetails.drug_id`, `importtdetails.drug_id` → `drugs.id`
- `{ancsheetinvest, gynainvestigation, gynasheetinvest, infertilitysheetinvest, mainantenentalinvest, mointoringsheetinvestigation}.investid` → `invests.id`; `invests.investcatid` → `investcats.id`
- `*.recepittmpid` → `recepittmp.id`; `receiptdrugs.receipt_id` → `recepittmp.id`; `recepittmp.doctor_id` → `awusers.user_id`; `recepittmp.drugstablename` = polymorphic table-name reference
- `importdetails.bill_id` → `importbill.id`

### Monitoring-sheet lookups (verified via LEFT JOINs in controller)
`mointoringsheet.{procedure, protocol, ejac, pesa, tese, sryo, hmg, agonist, hcg}` → `mointoringsheet{procedure|protocol|ejac|pesa|tese|sryo|hmg|agonist|hcg}.id`

### EAV (free-form investigations)
- `otherinvestigationsrows.patientid` → `patients.id`; `.doctorid` → `awusers.user_id`
- `otherinvestigationsvalues.investrowid` → `otherinvestigationsrows.id`
- `otherinvestigationsvalues.investid` → `otherinvestigations.id`

### Operations
- `operations.infertilitysheetid` → `infertilitysheet.id`
- `operativedetails.operationid`, `operativedetailsdrugs.operationid`, `operationids.operationid` → `operations.id`
- `operationids.{doctorid, assistantid}`, `operativedetails.{assistantid, assistant2id, anasthetstsid, anasthetsts2id}` → `awusers.user_id` (N:M surgery team)
- `operativedetails.{anasthesaid→anasthesa.id, generalid→generalanasthesa.id, jncisionid→jncision.id, midlineid→midlinejncision.id}`

### Ultrasound detail
- `gynausficils.gynausid` → `gynaus.id` *(verified JOIN)*
- `gynaus.mainantenatalid` → `mainantenental.id`
- `ultrasounddetail` / `ultrasoundobstdetail` → `ultrasound` / `ultrasoundobst` (per-fetus detail)
- `antenalvisit.mainantenentalid` → `mainantenental.id`; `.diagnosisid` → `diagnosisant.id`; `.complaintid` → `complaintant.id`

### Auth / RBAC (aw* family)
- `awusers.role_id` → `awrole.id`; `awusers.branch_id` → `branches.id`; `awusers.specialid` → `awrolespecialize.id`; `awusers.positionid` → `awroleposition.id`
- `awrolemenu.{role_id, menu_id}` → `awrole.id` ↔ `awmenu.id` (N:M)
- `awrolecontrollprop`, `awrolebtn` → `awrole` ↔ `awcontrollprop`/buttons (N:M)
- `awmenu.parent` → `awmenu.id` (self-referencing menu tree)

### Doctors board (b* family)
- `bsession.memberid` → `awusers.user_id`; `bsessionmembers.{sessionid, memberid}` = N:M; `bcomments.memberid`, `bmemberopenion.memberid`, `brequests.memberid` → `awusers.user_id`

### Misc
- `messages.{sender_id, receiver_id}` → `awusers.user_id`
- `device_tracking.{target_id + target_table}` = polymorphic reference to any table
- `presenthistoryanswers` joined via `gynaph.catid → presenthistoryquestions.id`, `gynaph.answerid → presenthistoryanswers.id` *(verified JOIN)*
- `patients` lookups: `wiftypeid→wifetypes`, `statuesid→wifestatus`, `wifeeducation/husbandeducation→education`, `wifejob→wifejobs`, `husbandjob→husbandjobs`, `wifebl/husbandbl→bloodtypes`, `husbandtypeid→husbandtypes` *(verified JOINs)*
- `ivfsheet.{ssemen, sseemen, sseemmen, sseemmeen}` → cloned lookup tables `ssemen`/`sseemen`/`sseemmen`/`sseemmeen` (one lookup table per column!)
- `registeration.{place2→place2.id, origin→origin.id}`

## 3. Mermaid ER Diagram (~25 central tables)

```mermaid
erDiagram
    BRANCHES ||--o{ AWUSERS : "awusers.branch_id"
    BRANCHES ||--o{ VISITS : "visits.branch_id"
    AWROLE ||--o{ AWUSERS : "awusers.role_id"
    AWUSERS ||--o{ VISITS : "visits.user_id / enddoctorid"
    AWUSERS ||--o{ PATIENTS : "patients.userid / doctorid"

    PATIENTS ||--o{ VISITS : "visits.patientid"
    PATIENTS ||--o{ ANCSHEET : "ancsheet.patientid"
    PATIENTS ||--o{ GYNASHEET : "gynasheet.patientid"
    PATIENTS ||--o{ INFERTILITYSHEET : "infertilitysheet.patientid"
    PATIENTS ||--o{ IVFSHEET : "ivfsheet.patientid"
    PATIENTS ||--o{ MOINTORINGSHEET : "mointoringsheet.patientid"
    PATIENTS ||--o{ FOLLOWUPCARD : "followupcard.patientid"
    PATIENTS ||--o{ PHOBSTETRIC : "phobstetric.patientid"
    PATIENTS ||--o{ TOTALBALANCE : "totalbalance.patientid"
    PATIENTS ||--o{ OTHERINVESTIGATIONSROWS : "otherinvestigationsrows.patientid"
    PATIENTS ||--o{ ULTRASOUND : "ultrasound.patientid"
    PATIENTS ||--o{ OPERATIVEDETAILS : "operativedetails.patientid"

    DETECTIONS ||--o{ VISITS : "visits.detectionid"
    VISIT_PERIODS ||--o{ VISITS : "visits.visit_period"
    VISITS ||--o{ VISITS_UPDATES : "visits_updates.visitid"

    ANCSHEET ||--o{ ANCSHEETDRUGS : "ancsheetdrugs.ancsheetid"
    ANCSHEET ||--o{ ANCSHEETINVEST : "ancsheetinvest.ancsheetid"
    ANCSHEET ||--o{ ANCNEWVISIT : "ancnewvisit.ancsheetid"
    ANCSHEET ||--o{ REGISTERATION : "registeration.ancsheetid"

    INFERTILITYSHEET ||--o{ NEWVISIT : "newvisit.infertilitysheetid"
    INFERTILITYSHEET ||--o{ TVS : "tvs.infertilitysheetid"
    INFERTILITYSHEET ||--o{ ICSI : "icsi.infertilitysheetid"
    INFERTILITYSHEET ||--o{ SEMEN2 : "semen2.infertilitysheetid"
    INFERTILITYSHEET ||--o{ OPERATIONS : "operations.infertilitysheetid"
    GYNASHEET ||--o{ NEWVISITG : "newvisitg.gynasheetid"

    MOINTORINGSHEET ||--o{ MOINTORINGSHEETVISITS : "mointoringsheetvisits.mointoringsheetid"
    MOINTORINGSHEET ||--o{ MOINTORINGSHEETDRUGS : "mointoringsheetdrugs.mointoringsheetid"
    FOLLOWUPCARD ||--o{ FOLLOWUPCARDDRUGS : "followupcarddrugs.cardid"

    DRUGS ||--o{ ANCSHEETDRUGS : "drugid"
    DRUGS ||--o{ MOINTORINGSHEETDRUGS : "drugid"
    DRUGS ||--o{ RECEIPTDRUGS : "drugs_id"
    INVESTS ||--o{ ANCSHEETINVEST : "investid"
    INVESTCATS ||--o{ INVESTS : "invests.investcatid"
    OPERATIONS ||--o{ OPERATIVEDETAILS : "operativedetails.operationid"

    OTHERINVESTIGATIONSROWS ||--o{ OTHERINVESTIGATIONSVALUES : "investrowid"
    OTHERINVESTIGATIONS ||--o{ OTHERINVESTIGATIONSVALUES : "investid"
```

## 4. Design Patterns

1. **Sheet pattern (core design):** parent sheet per specialty + cloned standard children: `*drugs` (drugid, drugtype, drugdos, forhusband, recepittmpid, recepitdrugid), `*invest` (investid, investresult, forhusband), sometimes `*us`/`*visits`. Instances: ancsheet, gynasheet, infertilitysheet, mointoringsheet, mainantenental, followupcard, gyna (gynadrugs/gynainvestigation), infertility (infertilitydrugs/infertilityinvest), operativedetails (operativedetailsdrugs). Structure duplicated per specialty rather than unified with a type column.
2. **Lookup pattern:** ~190 of 312 tables (61%) have ≤4 columns, shape `id + title/name + del`. One table per dropdown, even per-column clones (the `ssemen` quadruplet; 9 `mointoringsheet*` lookups; `copy*` hysteroscopy lookups; `semen2result*`/`semen2place*`; `lapar*` site lookups; `tvs*`, `hsg*` finding lookups).
3. **EAV pattern:** `otherinvestigations` (attribute catalog) / `otherinvestigationsrows` (entity row: patient+date+doctor) / `otherinvestigationsvalues` (value: investrowid+investid+value).
4. **Copy-table audit pattern:** `patients_updates`, `visits_updates` = full structural clones + `userid_edit`/`date_edit`.
5. **Inconsistent soft delete:** `deleted` / `del` / `tempdelete` / `status` used interchangeably across tables.
6. **Polymorphic references:** `device_tracking.target_id + target_table`; `recepittmp.drugstablename`.
7. **No visit linkage:** clinical records join to patient + date, never to a visit row.

## 5. Duplicate / Legacy / Abandoned Tables

| Group | Tables | Note |
|---|---|---|
| Dev leftovers | `table2`, `table3`, `tablename`, `testtbl1`, `testtbl2`, `patients_tmp` | No controller usage; safe to drop after verification |
| Quadruplet clone | `ssemen`, `sseemen`, `sseemmen`, `sseemmeen` | Identical lookups, one per `ivfsheet` semen-source column |
| Near-duplicate pairs | `antype`/`antypes`, `place`/`place2`, `importdetails`/`importtdetails`, `hormonalprofile`/`hormonalprofile2`, `semen`/`semen2`, `excelinfo`/`excelinfopatients` | Successive generations kept side by side |
| `copy*` family | `copycavity`, `copycx`, `copydil`, `copyintrod`, `copylostium`, `copyrostium`, `copyplace`, `copymcavity`, `copymcx`, `copymlostium`, `copymrostium` | Hysteroscopy lookups that began as copies and became canonical, each with an `m*` second copy |
| Historical archive | `old_visits` | Pre-branch/pre-period visits structure, read-only |
| Parallel ANC generations | `ancsheet`+`ancnewvisit` vs `mainantenental`+`antenalvisit` vs `followup`+`followupvisit` | Three coexisting antenatal modules, each with own drugs/invest children |
| Defined but inert | `patientfiles`, `help`, `messages`, partially `patients_updates` | Schema exists; active code bypasses or never writes (inferred from controllers) |

## 6. Verification Evidence (controller JOINs)

- `JOIN visits ON patients.id = visits.patientid` / `JOIN patients ON visits.patientid = patients.id` (multiple controllers)
- `LEFT JOIN detections ON visits.detectionid = detections.id`
- `LEFT JOIN mointoringsheet{tese|sryo|protocol|procedure|pesa|hmg|hcg|ejac|agonist} ON {lookup}.id = mointoringsheet.{col}`
- `JOIN gynaus ON gynaus.id = gynausficils.gynausid`
- `LEFT JOIN ancsheet ON ancsheet.id = list.ancsheetid` (op_4d_list)
- `JOIN presenthistoryquestions ON presenthistoryquestions.id = gynaph.catid`; `JOIN presenthistoryanswers ON presenthistoryanswers.id = gynaph.answerid`
- `LEFT JOIN wifejobs ON wifejobs.id = patients.wifejob`; `INNER JOIN husbandjobs ON patients.husbandjob = husbandjobs.id`
- `JOIN phpastsurgicaloperation ON phpastsurgicaloperation.id = phpastsurgical.surgicaloperation`; same for `phpastgynecologicaloperation`, `phpastarttype`
- `LEFT JOIN Operativedetails ON Patients.Id = Operativedetails.Patientid`
- External ERP link: `accountstree.name = client.clientname WHERE obygyPatientId = ...` (cross-database ERP sync)