I have implemented a database for a taxi company. I have used PostgreSQL to create this database. I have 4 entities customer, firm, driver and bookings. Currently I am having trouble trying to find a way to calculate how many drivers there are for a certain firm.
In the driver entity a foreign key is used which is the 'firmid'. The 'firmid' creates a link which tells the database that a certain driver belongs to a certain firm.
In the firm table I have a column which is named 'no of drivers'. Currently I have to count the number of drivers manually that belong to a specific firm from the driver entity and input the integer into the column in the firm entity. Is there any kind of formula or some function that I can implement that can automate this process.
e.g If I was to delete a driver from the driver entity, the number of drivers in the firm entity for a specific record should decrement and vice versa for adding a driver.
This problem has really been bothering me for a week now.
UPDATE 20/12/2014
I have tried adding a constraint to check the number of drivers:
ALTER TABLE firm ADD CONSTRAINT numberofdrivers_check CHECK (driversno ~ (SELECT COUNT (*) AS drivers FROM driver d GROUP by d.firmno ORDER BY 01));
But I get the following errors:
ERROR: operator does not exist: bigint ~ bigint
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Any ideas?
I'd recommend against storing an aggregate value like this in the database. Just calculate it when you need it.
SELECT d.Firm_ID, count(*) AS Driver_Count
FROM Drivers d
GROUP BY d.Firm_ID