# Database normalization and structure (suggested)

Current main tables:

- `bins_ingresso`
- `bins_produzione`
- `boxes_produced`
- plus support tables for logs, users, etc.

## Suggested reference tables

To make filters and reporting easier:

- `growers` — one row per grower (code, name, contact, etc.)
- `varieties` — one row per fruit/produce variety
- `lines` — packing / production lines
- `operators` — employees / operators

Then reference them by foreign keys from `bins_ingresso`, `bins_produzione`, `boxes_produced` instead of repeating free-text strings.

## Example: growers

```sql
CREATE TABLE growers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(32) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE bins_ingresso
  ADD COLUMN grower_id INT NULL,
  ADD CONSTRAINT fk_bins_ingresso_grower FOREIGN KEY (grower_id) REFERENCES growers(id);
```

## Reporting views

Create SQL views for common dashboard queries, e.g.:

```sql
CREATE VIEW v_bins_dumped_daily AS
SELECT
  DATE(created_at) AS d,
  grower,
  variety,
  SUM(quantity) AS bins
FROM bins_produzione
GROUP BY DATE(created_at), grower, variety;
```

Then the PHP dashboard / APIs can query from the view instead of repeating long GROUP BY queries.
