General Db set-up question.

[bazz]

Hi there,

I need your help with this structural issue.

Imagine I have several clients. Each has a similar database and some of their tables are the same for all clients.

Would it add efficiency if I merged all common tables and put them into a common location where each clients data could be held.

It opens up more opportunities for more client benefits but I need to measure any negatives against these and that is what i ask your advice on.

q1. if I make a common db table, (which could then have x-million records x #clients), is that better or, should I keep all clients db's separate irrespective of any 'commonality'?

q2. some tables are small and are needed by each clients db. rather than me having to up date each clients db, I want to be able to update a table once (eg VAT rates per year). Are there any difficulties / issues, with making this type of table centralised.

I appreciate any views or advice you may have because I can't think of an adequate search term, where I can read all the results within my lifetime. :)

bazz

[Stooshie]

If your tables are properly indexed etc, and tables optimised and your queries have been made efficient then you should be OK it will also depend on how many times the table is being hit with queries.

Is there a possibility of archiving some of the rows?

Also try caching some of your queries.

However, millions of rows * client num (100?) adds up to quite a large number of rows. That could really start to get slow, even with all the optimisations.

Willl it definitely be millions of rows per client?

[bazz]

Thanks for the reply stooshie.


Willl it definitely be millions of rows per client?


in a year it could be 10 000 000 records in one table, just for the clients I have attracted so far.

I'll look into archiving records in that table when they are 12months old but, (because I haven't learned that feature yet) I need to be sure that when archived, they can still be accessed, if needs be, for historic queries.

If archiving won't 'do it', I think, I'll have to find a way to keep each businesses records in separate tables and use a query to check them all, when such a necessity arises.

bazz