billing database design and performance

[Fabio Balzano]

i need to resolve a db design problem allowing good performance too.
I have got a billing db that grow 50000 new rows every day.
I thinked to prepare:

-a big single table for the new rows because the application that produce the values needs to run fast;

-a table for each user filled with a trigger that go fast every new row copying the data from the big table to the personal table;

the users are 1000 more or less and each day:

-100 of them produce each one 250 new rows into the table;
-300 users 100 new rows;
-500 users 10 new rows;

everybody need to know the balance every day reading it from another big table,
furthermore they need to query the personal table for historical purposes reading the entries of the month.All the users query (SELECT) the db during the day quite often.

Is this the right design aproach to the problem?
The trigger can do the job serving so many rows in short times( ~20 per second )?
which table engine i have to use?
Is it better split in many tables or a big big table for all the users? or split the rows in other ways?
some tips for tuning mysql?(cache, how many Gb ram)

thank you a lot for your help in advance and sorry for my english
fabio balzano



Edited 1 time(s). Last edit at 09/10/2006 06:06AM by Fabio Balzano.