Database table design

scorp_scorp

Dear All, 1st, happy new year for all of you. I have 2 tables, X and Z. In real life, X contains one numerical field/coloumn that is the sum of 900 numerical inputs/records from table Z. My question is, when designing the table X, should i include a feild/coloumn that contains the sum of the inputs/records in Y, or not, little bit confused :confused: Regards...

0 will always beats the 1.

Hiren solanki

scorp_scorp wrote:

My question is, when designing the table X, should i include a feild/coloumn that contains the sum of the inputs/records in Y, or not, little bit confused

There's one option available of computed column in SQL Server, That's just giving you column value by applying formula you've provided while creating that column rather then storing physical data of SUM.

Regards, Hiren.

My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Building Hierarchy using Recursive CTE

scorp_scorp

Thanks for the quick reply hiren, but, what i mean, is, if it is a good practice from a design point of view. Since some told me that i shoudnt, since i can get the sum in a report, query, or in a view, any time and then, i shouldnt save it in a table. What do u think?? regards,

0 will always beats the 1.

Hiren solanki

Storing it physically though same data you can get that data anytime by querying isn't a good practice at all. But What I mean is Computed column is invented for that purpose only it just computes value provided and gives you final answer in a column looks like it's physically storing data, but it isn't it's just a logical.

Regards, Hiren.

My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Building Hierarchy using Recursive CTE

scorp_scorp

Hiren Solanki wrote:

a column looks like it's physically storing data, but it isn't it's just a logical.

Got the point, thanks a lot, very helpfull. Regards,

0 will always beats the 1.

Lost User

As Hiren noted, a computed column would be a good solution. That would be re-evaluated every time you fetch the records, adding a performance-penalty. I'd go for a column in table X that would get updated with a trigger on table Z. That way the server only has to recount the new records once. The downside is that this would add more complexity than a computed column.

I are Troll :suss:

PIEBALDconsult

A summary table is a good solution when the calculations are very complex or you delete/archive the data. Is that what you're doing?

jschell

Hiren Solanki wrote:

Storing it physically though same data you can get that data anytime by querying isn't a good practice at all.

That isn't true - it depends on explicit and implicit business needs. One consideration is volume and the load on the database. If summaries are common and significant in volume while being exactly the same summary (such as daily totals) then summing them once and then reserving that value can reduce load. Another consideration is historical retention. It might not be possible or might be too complex to attempt to retain complex calculations which have inputs that vary over time. So the calculation is done at a point in time that is valid and then retained for later use to insure that reproducing reports (receipts or whatever) will still produce the same value.

jschell

scorp_scorp wrote:

In real life, X contains one numerical field/coloumn that is the sum of 900 numerical inputs/records from table Z.

Does table Z have 900 rows or 900 billion? Is table Z used in other queries that run once a day or 1000 times a second?

scorp_scorp

for each record in X, the amount of numerical feild correspond to sum of records "not more than 900 record" in Z. ie: table Z has more than 900 records (unlimited), but the sum in X is limited to a max number of 900 in Z. Table Z is subjected to quireis, that accour not more than once a day. Regards,

0 will always beats the 1.