Hi,
Before writing this mail, I'd researched a little about this topic, and
got some opinions from guys like Fabien Pascal, who argues that logical
design should be separated from physical design, and other sources. As
this is not fact, I'm writing to you guys, that make things work in real
world.
We started our first big (for our company standards) project always
thinking in normalization. But once we imported legacy data into the DB,
things got harder.
One example is the clients status. A client might be active, inactive or
pending (for many reasons). We store all the status a client have since
it is in the system. To check what is the actual status of a client, we
get the last status from this historical status table. This take a
considerable time, so our best results were achieved building a
function that checks the status and indexing this function. The problem
is that indexed functions mus bu immutable, so as you can figure, if the
status change after the creation of the index, the retunr of the
function is still the same.
What do you suggest for situations like this? Should I add a field to
clients table and store its actual status, keep storing data in the
historical table an control its changes with a trigger?
There are other situations that are making things difficult to us. For
example, one query must return the total amount a client bought in the
last 90 days. It's taking too long, when we must know it for many
clients, many times. So should I create summarization tables to store
this kind of stuff, update it with a trigger in daily basis (for
example), and solve this problem with one join?
Our database is not that big. The larger table has about 7.000.000 rows.
About 50.000 clients, half of them active. All that I'd point out above
uses indexes for queries, etc. But even with this it's not been fast
enough. We have a Dell server for this (I know, the Dell issue), a Dual
Xeon 2.8, SCSI HD, 1 GB mem. Do we need better hardware for our system?
--
+---------------------------------------------------+
| Alvaro Nunes Melo Atua Sistemas de Informacao |
| al_nunes@atua.com.br www.atua.com.br |
| UIN - 42722678 (54) 327-1044 |
+---------------------------------------------------+