Re: counting rows

> So I guess what you are saying is: given that there IS a column called
> numPictures in table Users anyway, it is best to use it. You are not
> suggesting that such a column should generally be created and
> maintained, which is what I inferred from your answer?

I'm simply answering the question from an implementation and scalability point of view rather than relational theory.

I am saying, if performance and scalability requirements dictate (implementation of the logical design) then yes, a number_of_pictures column should be created, whether that feeds back into the logical model I'm not sure as its an implementation thing, in the say way you can argue surrogate keys are an implementation thing and shouldn't be in the logical model.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Tony Andrews" <andrewst_at_onetel.com> wrote in message 
news:1143975423.186794.160040_at_e56g2000cwe.googlegroups.com...
>> "Tony Andrews" <andrewst_at_onetel.com> wrote in message
>> news:1143891453.767287.295100_at_i39g2000cwa.googlegroups.com...
>> > Tony Rogerson wrote:
>> >> Number 2 will be best because you are effectively storing the 
>> >> aggregate
>> >> instead of the server having to count the number of rows in the table
>> >> which
>> >> would require IO.
>> >
>> > That is wildly simplistic.  Based on that advice, it would be "best" to
>> > always store the count, sum, min, max, avg, etc. of everything in its
>> > parent table(s).  Please tell me you don't!?
>
>> Tony Rogerson wrote:
>> When somebody asks this...
>>
>> >>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?
>>
>> I give an answer to THAT question and not add in other stuff, if the OP
>> wanted min, max, sum etc... then I'd answer differently.
>


> So I guess what you are saying is: given that there IS a column called


> numPictures in table Users anyway, it is best to use it.  You are not

> suggesting that such a column should generally be created and

> maintained, which is what I inferred from your answer?

>