MySQL foreign key help

[Acorsys]

Greetings,

I am creating a MySQL/PHP database for a small project and I need some help on creating foreign keys so I can relate them to the criteria we need.

I have two tables I need help with,

Division
- divisionID (pk)
- name
- description
- number_of_pumpstations (gathered from pumpstation table)

PumpStation
- name (pk)
- divisionID (foreign key to division)
- components

Any help would be appreciated. Basically all I need is to know how to make the number_of_pumpsations column gather all PumpStations eqaul to the divisionID and make the divisionID field in the PumpStation table grab the information from the Division table. I hope that is somewhat clear...thanks.

[barand]

Don't hold calculated data in the database - it can always be calculated when you need it.

As in this, which probably answers your 2nd question too :-

$sql = "select d.name, COUNT(p.name) as number_of_stations
from division d INNER JOIN PumpStation p
ON d.divisionID = p.divisionID
GROUP BY d.name";

hth