Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2007-09-05 : 02:33:11
|
| Hi,I'm having a DB designed for me, and I'm inspecting it and wondering what in general is the better way to do this.We have a product, which we are counting "product views". The DB designer has created columns called "view_today" and "views_alltime".I specified I wanted a normalized database, I'm thinking this is technically not normalized ? Am I correct ?Wouldn't it be better to have a query that counted the views off the logging table ? I can't see any advantage to doing it the way its been designed except to save time.Thanks for any input !Mike123 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-05 : 03:37:27
|
| "except to save time"That could be a biggie!If the logging table is highly active there will be blocking issues when trying to query it, and it will take time if the log is large. It can be better, for performance, to keep aggregate data to reduce the resources needed for Product queries.Personally I wouldn't store this in the Product table. I would want the product table to have minimal updates - so I wouldn't keep highly volatile data such as current stock level and view-today, I would put them in a parallel table.The other thing that having a view_aggregate column helps with is purging of the old logs. Presuming that you do not want to, or cannot afford the disk space for!, keeping the logs forever then storing the aggregates allows purging of stale data (well, not really for "views_today", but I'm sure you get my drift)I'm with you on normalisation, but concessions to performance and do-ability need to be considered too.Of course if you ask ten different database folk you will get between 1 and 10 different answers!Kristen |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2007-09-05 : 04:23:09
|
| Hi Kristen,Sorry I meant "development time", I'm not too concerned about saving that :)Great answer tho, I appreciate your point of view. Would you say that by storing the data this way the database is not 100% normalized ?Thanks again,mike123 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-05 : 05:15:10
|
| "Would you say that by storing the data this way the database is not 100% normalized ?"Yes, but that would not bother me if it was guaranteed to keep in sync.The usual problem with non-normalised data is that you have a field to type, say, country - instead of picking country from a list. Then you get different spellings for the Country and can;t report reliably on it.If the views_aggregate stuff works reliably then it wouldn't bother my sense of "data only in one place" at all. I know I'm a bit of a maverick on this, but I drop normalisation whenever it helps the development process, or run-time performance (but I would never provide the user with an ability to type the same data in two places! - I mean like the Country example above)Kristen |
|
|
|
|
|
|
|
|
storing a value, vs computing a value