If you have a one to many relationship, say a product has many reviews, is it good design to have a flag in the product which indicates whether there are any review in the reviews table?
One might do this so that in a stored procedure one can check to see if there are any product reviews before running a select query on the reviews table. If there aren't then you've saved a select query.
Also, this flag could be used to determine the type of formating required for the data. I suppose it would be quite easy to just look up the count of reviews in the data table to gather the same info.
Cheers,
I.I hate designs like that. That sp is still going to have to query the product table to check the flag, so you haven't gained a thing. A simple Count Where fk=pk will tell you just as fast, or just do the query--you're going to want the records anyway.
The only way I could see this helping would be if ALL of the following conditions were met:
** This is an extremely active database that is being pushed very hard
** It is rare for there to be any reviews
** You already have the product table row
But what a headache to try to keep that flag updated. Maybe a trigger could be used -- add a record, increment the count, delete a record, decrement the count.|||It didn't feel right either.
I think another problem with it is that if a sp determines that there are reviews to gather and then a concurrent user goes and deletes all the reviews, the first sp will return no reviews so the formatting will be set up to show reviews but they'll be none.
"That sp is still going to have to query the product table to check the flag, so you haven't gained a thing."
If you had several flags for several tables then it might save a few selects. You could get all the flags in a single select.
No comments:
Post a Comment