How BIG?

[Saucepan]

im constructing a database that consists of some pretty large tables; contacts, products, orders and so on. before i get to deep ive got one fundamental question: how big is too big?

what i mean by this is offcourse how much info should i put in one database, and when (and why) should i split it up into different files? whats the pros and cons by having one big database "COMPANY", with different tables called "people", "orders", "products", and so on, in view of having many different databases "PEOPLE", "ORDERS", "PRODUCTS" and then linking them all together?

i know this is very different from time to time, but i just want some pointers to start me of in the right direction...

Edited July 10, 2006 by Guest

[Ender]

For small and medium sized solutions (less than 25 tables), it's generally easier to deal with multiple tables in one file. For larger solutions or situations where you have some very large tables (millions of records), I'd suggest breaking things into logical modules.

So I guess the question is, "How BIG is your database?"

[Saucepan]

well...

its not nearly in the millions per table, but summing up all the records in all my (nearly 25, im planning on 20 so far) tables, im breaking the million-mark.

thats the thing though, i really have to consider all the possible "hows" in constructing this, taking in to effect the fact that i most likely am going to expand the database with further records. thats why i really would like an indept description of the pros and cons...

so to (poorly) answer your question... fairly?

[Razumovsky]

For larger solutions or situations where you have some very large tables (millions of records), I'd suggest breaking things into logical modules.

I appreciate the impulse here, but am uncertain about exactly what the benefits would be in splitting into modules for larger records. Speed in backing up is about the only thing that comes to mind, and I do not know how much it is impacted by a modular approach. I have not dealt with such a large record database before - Does anyone have experience they could share on this?

[Søren Dyhr]

Chances are, that the need for:

some pretty large tables

...points in the direction of a better and more normalized structure might exist somewhere in the mist. It's almost suggests redundant storage.

--sd

[Ender]

There are several issues that come up with large, complex solutions, where a modular design would benefit:

1. Upgrades. If a section of the solution needs a complex upgrade, this is safest to do in an offline clone. When the upgraded file is ready to put into place, you bring the live file down and import the records from each table into the upgraded file. With a modular approach, only the module being upgraded needs to go through this (maybe 5 tables instead of 50 or 60 for the entire solution).

2. TOG Readability. In a modular design, the TOG for each module only need include those tables that are relevant to that module. If the entire solution is in one file, the TOG becomes rather unwieldy.

3. Maintenance and Recovery. In a modular design, only the problem file will need to be compacted or recovered, a process that can be very lengthy on very large files. If the problem module isn't a key part of the rest of the solution, the users may even be able to continue in other parts of the solution while the module is offline for maintenance.

4. Backups. In some environments, there may be modules that are more active and more business critical. If delays from a backup of the entire solution are noticable and inconvenient, then it may be desireable to backup the different modules at different times or frequencies.

The issues with very large tables are similar to having a large number of tables. They take longer to import into an updated file, take longer to recover, and take longer to backup.

[Razumovsky]

Thanks Ender, all good points to consider.

[Saucepan]

yepp... i agree with Razumovsky, seems to be alot to consider... i was also (not that its very important in m case) concerned with security. is it safer to split the records?

[Ender]

For securing your data, it makes no difference whether you use one file or many.

The consideration with security is the additional work of adding the privilege sets and accounts to each file. Managing accounts accross multiple files can be done with the account management script steps, but the privilege sets must be set up manually. The scripting involved for propogating account changes to multiple files is fairly complex, which is why for a multi-file solution it's useful to explore the external authentication option.

External authentication centralizes accounts on a directory server. Then you only need to manage the privilege sets in each file.