Divide and Conquer
This Thursday is Thanksgiving in the US. There will be much to do. Someone has to prepare the turkey, and then someone has to cook it. Someone has to wash and peel potatoes before they are mashed and cooked. Then there’s the cranberry sauce, the homemade rolls, and the green jello salad that Uncle Saul argues is really desert. There is much to do. How do you do it? No, don’t make Mom do it all! What an ungrateful act; this is Thanksgiving, a time when we should divide and conquer.
Divide and conquer has many applications besides the kitchen and the battle field. Did you know that James Madison, in a letter to Thomas Jefferson, argued that the way to administer a large republic on just principles was to divide and conquer? Consequently, the Founding Father’s divided the government into three separate branches – thus ensuring that no one person or group will dominate and control the government.
The idea of divide and conquer also can be applied to a database. One can dramatically improve performance by splitting the database into two files: a front-end file and a back-end file.
What is a back-end file?
The back-end file is also called a data file because it contains only the tables that hold all the data. The back-end file is usually stored on a local file server or in the cloud, thus enabling all authorized users to get to the data they need.
What is a front-end file?
The front-end file is also called an interface file because its purpose is to provide the users an easy to use interface. This interface is composed of forms, and reports, as well as needed queries, macros, and programming modules. The front-end will also contain links to the data tables in the back-end data file. The front-end file is usually stored on each individual user’s desktop.
What are the advantages of having a split database?
Sharing data – By having the interface split from the data, the data can easily be stored on a file server where all authorized users can share it.
Improved Network Performance – When the database is not split, the network slows down because to each user it must transfer 1) data, 2) user interface forms, 3) macro code, 4) programming code, 5) query code and 6) report definitions. In contrast when the database is split, the queries, forms, report, macros and programming code are already on the user’s workstation, so only the data needs to be transferred across the network.
Improved reliability / productivity – Unfortunately, computers sometimes crash. With a non-split database, a crash on one user’s workstation can corrupt the database for all users. With a split database, a single user’s system crash will only affect that one user. All others will be able to continue their work.
Security – By splitting a database, users do not have direct access to the database tables. This limits their ability to accidentally delete tables or worse, copy all the data to give to a competitor.
So the bottom line is, divide and conquer. It is a great way to improve the efficiency, security, and effectiveness of your database, your country, and even your Thanksgiving dinner.