Manage Learn to apply best practices and optimize your operations.

How to determine whether to store data in single database file or multiple files

How do you determine if data should be stored in a single database file or if you should use multiple files? For example, if my client requests a program for a LAN-based system for POS involving inventory and vendors, would I use multiple files or just multiple tables in a single file?

In my opinion, if a developer can tell whether their tables are stored in one file or multiple files, that is a problem in itself.

Ten years ago, the custom application development industry was mostly data file based. Various data management packages were fighting for supremacy, with no clear leader. At that point in time, whether you wanted to keep all of an application's data in a single file or multiple files was a valid question.

SQL has become the "Lingua Franca" of data access. There is now one more or less standard DML (data manipulation language) to construct queries to manage your data. The SQL standard lead to the evolution of new opportunities...

There are now several inexpensive, easy-to-use client/server database engines. Microsoft has MSDE, Sybase has SQL Anywhere, IBM has UDB (basically DB2 for mortals), and Oracle has an assortment of workgroup-oriented products. One of these almost has to address the needs of a workgroup application, while preserving the ability to "grow" into a full-blown database server if the application grows to the point that it needs one.

By going to a client/server arrangement, you get a lot of benefits. First of all, it becomes MUCH easier to safeguard your data since it now exists in only one place. More important than the physical isolation, all of the client/server engines that I have seen support backup and restore options that file-based systems can't. A nice side benefit of this arrangement is that network traffic can also be greatly reduced with good client/server design.

File-based databases are inherently fragile. If your connection to the file server is broken, you leave the data file in a corrupt state. If someone needs to clear space off of the network, and sweeps *.mdb into oblivion, your data could disappear. If your boss leaves the application running in their office with the door locked, you have no way to backup the database. Heck, if one of your workstations has a bad memory chip or somebody trips over the power cord while it is writing to your file, it could totally trash your database!

If you must stay with a file-based database, I'd recommend using a single file for each application or logical segment of a larger application. For example, an accounting system could use either a single file if the database would support the number of tables and amount of data needed, or it could split so that General Ledger was one file, Accounts Receivable was a second file, Accounts Payable was a third, and so on.

For More Information

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.