Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I have two tables, foo and bar:

CREATE TABLE foo(
   foo_id    serial    NOT NULL,
   count     integer   NULL,
   PRIMARY KEY(foo_id)
   );

CREATE TABLE bar(
   bar_id    serial    NOT NULL,
   foo_id    SERIAL    NOT NULL REFERENCES foo(foo_id),
   PRIMARY KEY(bar_id)
   );

When something gets inserted into bar with a given foo_id, the corresponding record in foo should have its count incremented. How exactly do I go about doing this? I'm fairly new to databases in general.

For example:

INSERT INTO foo VALUES(1, 0);
INSERT INTO bar VALUES(1, 1);

At the second line, count should be incremented at the record for foo_id = 1.

I also need to the count decremented on a delete, but I'm sure that's pretty similar to incrementing on insert.

share|improve this question

migrated from serverfault.com May 4 at 1:47

This question came from our site for professional system and network administrators.

4 Answers 4

Recognising that this count is denormalised, and assuming you need it for some purpose that can't be obtained on-the-fly at run time, such as a COUNT() or EXISTS(), I would suggest you use a trigger on bar. More specifically, three triggers, one each for update, insert and delete since each will have a different effect on foo. Note, too, that you may need equivalent triggers on other bar-like tables which also have a foreign key pointing back to foo, depending on what your specific needs are.

In this case a trigger has the advantage of separating "proper" business rules (which should be in a stored procedure) from system optimisation. The former can be maintained by developers without the latter cluttering the code. Secondly, there is less risk that the optimisation code is compromised during a code change. Third, future SPs cannot forget to implement this optimisation or choose not to implement it. The trigger is there for all usage of the table. If there is a case when the count should not be updated that can be made explicit in the trigger code and well commented there for all to see.

share|improve this answer
    
While I agree with you, I have to add a con: with many of these triggers it will be very hard to follow what happens on a simple INSERT. I used to spend much time debugging funny behaviour, partly induced by my denormalization triggers. –  dezso Nov 12 at 9:23

Follow your INSERT statement with:

UPDATE bar SET count = (count+1) WHERE foo_id=1

And on delete:

UPDATE bar SET count = (count-1) WHERE foo_id=1
share|improve this answer
    
.. and wrap all these statements in a transaction, with good error trapping and handling. –  Michael Green Nov 12 at 1:46

I think you need to question why you are storing a count on the foo table. You will be able to retrive the number of bar records referencing any given foo_id at a later date by using SELECT COUNT(*)...

share|improve this answer

You should be able to retrieve the count by running

SELECT COUNT(*) FROM foo 
INNER JOIN bar
ON foo.foo_is = bar.foo_id
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.