Re: counting rows


"paul c" <toledobythesea@xxxxxxxx> wrote in message
news:h7%Wf.201992$sa3.129417@xxxxxxxxxxx
bucaliman@xxxxxxxxx wrote:
Consider the tables USERS and PICTURES. When retrieving the number of
pictures from a user there are two ways:

1) I count them from pictures table.
2) I pick it directly from a field "numPictures" in table users.

How are you used to do this? Which is better?
Thanks


In a way, it's like the riddles about changing light bulbs (the one I
like best is 'how many psychiatrists?' - answer 'only one but the light
bulb has to want to change'). When even mainframes took a second or two
to count to a million it seemed the fastest way to do that was store
(pardon me, I should have said 'memorize') the number in the database.
I think it depends on what the number means to you and what if anything
its production means to you. I don't believe that any DB dogma has
anything useful in the sense of right or wrong to say about this.

pc

It depends.

Number 1 is better from the point of view of managing redundancy. Number 2
stores a single fact in more than one place. In this case the "fact" is a
count of rows and also the recorded count.

If you mismanage redundancy, unlikely in this case, you can end up with a
database that contradicts itself.
(Where the recorded count doesn't match the actual count).

Number 1 is also better from the point of view of data capture. There is
less work to do when inserting a new picture.

Number 2 is faster for retrieval. It will generally take more work to count
the pictures than to read a recorded count.

If number 2 is too slow at picture insert time, you can sometimes defer
adding one to the recorded count, but you have to make sure that the
recorded count doesn't get used when it's out of date. (Again, redundancy
mismanagement).

For small numbers, this will be a trivial difference. For millions of
pictures, it will make a difference.





.