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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have a Database Design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Datahase Design questions--or help out your peers by answering them--in our live discussion forums.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Pat Phelan
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.