Normalizing & Data Arranging

[mcox05]

I am newer to Database programming and my question is this:
Is it better (when you expect your access files to be extremely large) to normalize not only tables but maybe place those tables in seperate files? Intuitively it seems so to me that way you can receive queries from multiple users spread out accross a multitude of files rather than bottlenecking it with one huge file.

 

[gemma-the-husky]

i would have thought it better to keep all your files in one database unless you HAVE to split because of dbs size issues.

one reason is that you couldn't enforce referential integrity, i think, with data in different files (although I may be wrong there).

To prepare a query , Access still has to do (at least) the same amount of work whether the data is in one or several files.

 

[OllyK]

If the size or user base is going to get that large, it may be worth considering SQL or mySQL from the outset.

 

[dsigner]

Sooner or later any large database will hit capacity problems as it grows. Keep the structure simple with a few large files which are correctly related. This will be the easiest for porting to new software/hardware when you reach the limit on the existing setup.

 

[The_Doc_Man]

when you expect your access files to be extremely large

When "relatively large" is measured in any units less than 100 Mb, you are not too large for Access as long as you do a very methodical periodic compression and backup regimen on the database.

Using multiple files, you can sometimes have a slight performance penalty. With a single file, if the tables are carefully indexed to match your most frequent types of searching, you should not see a performance hit.

Using multiple files, you can lose data if you lose the file. The question then becomes whether you lose the WHOLE DATABASE because the file you lost was critical. With a single file, a good backup regimen means you never lose more than a day's work.