Derived Fields
-
More of a poll than a real question, I guess, but here goes: I have a database that contains information on how to, and how much it costs, to repair something. The cost is derived by adding the cost of all materials and all labor associated with a repair. The "defect" is stored in one table, and it's associated repair steps, materials, etc... are stored in other tables in a one to many relationship. Long story short, every time I retrieve the cost of one of those defects, which is quite frequent, I have a UDF that figures the cost. I was thinking about storing that derived total cost as a field instead of processing the cost each time, as it would only be calculated each time the defect is changed or saved, vs. every single time someone requests the defect. I am generally a data purist and the thought of storing data I can derive chafes me, but it does seem like a lot of over processing. What are your thoughts? Right now the table is only about 1500 records and it is unlikely to grow larger than that.
-
More of a poll than a real question, I guess, but here goes: I have a database that contains information on how to, and how much it costs, to repair something. The cost is derived by adding the cost of all materials and all labor associated with a repair. The "defect" is stored in one table, and it's associated repair steps, materials, etc... are stored in other tables in a one to many relationship. Long story short, every time I retrieve the cost of one of those defects, which is quite frequent, I have a UDF that figures the cost. I was thinking about storing that derived total cost as a field instead of processing the cost each time, as it would only be calculated each time the defect is changed or saved, vs. every single time someone requests the defect. I am generally a data purist and the thought of storing data I can derive chafes me, but it does seem like a lot of over processing. What are your thoughts? Right now the table is only about 1500 records and it is unlikely to grow larger than that.
If the system performance is currently acceptable and is unlikely to change based on data growth, they why change the system? However, if you view this as an opportunity to improve the system design and it will not adversely affect other work or performance, then change it.
-
More of a poll than a real question, I guess, but here goes: I have a database that contains information on how to, and how much it costs, to repair something. The cost is derived by adding the cost of all materials and all labor associated with a repair. The "defect" is stored in one table, and it's associated repair steps, materials, etc... are stored in other tables in a one to many relationship. Long story short, every time I retrieve the cost of one of those defects, which is quite frequent, I have a UDF that figures the cost. I was thinking about storing that derived total cost as a field instead of processing the cost each time, as it would only be calculated each time the defect is changed or saved, vs. every single time someone requests the defect. I am generally a data purist and the thought of storing data I can derive chafes me, but it does seem like a lot of over processing. What are your thoughts? Right now the table is only about 1500 records and it is unlikely to grow larger than that.
You mean like a calculated field? http://msdn.microsoft.com/en-us/library/ms345330(v=SQL.90).aspx[^]
-
More of a poll than a real question, I guess, but here goes: I have a database that contains information on how to, and how much it costs, to repair something. The cost is derived by adding the cost of all materials and all labor associated with a repair. The "defect" is stored in one table, and it's associated repair steps, materials, etc... are stored in other tables in a one to many relationship. Long story short, every time I retrieve the cost of one of those defects, which is quite frequent, I have a UDF that figures the cost. I was thinking about storing that derived total cost as a field instead of processing the cost each time, as it would only be calculated each time the defect is changed or saved, vs. every single time someone requests the defect. I am generally a data purist and the thought of storing data I can derive chafes me, but it does seem like a lot of over processing. What are your thoughts? Right now the table is only about 1500 records and it is unlikely to grow larger than that.
Personally I'm with Tim, I consider your design the technically correct one. Technically correct is not always the best design, it is the one I prefer but if I thought storing the data would give me a better solution I'd have no compunction in changing it. Only you can asses the benefit to changing but I would think 1500 is a trivial number to deal with, add a few zeros and I'd look at it again!
Never underestimate the power of human stupidity RAH