Front End / Back End split - where do you keep your tables? (1 Viewer)

[Keith Nichols]

Hi folks,

The database splitting utility puts all tables in the back end.

I read somwhere that you should put tables with records that will be updated in normal use (live data) in the Back End and tables with fixed data that is infrequently updated in the distributed Front End.

In my DB, Projects, Personnel, & junction tables etc. are in the Back End, Assets, Job titles and project categorizations etc. are in the Front End.

I think the idea is to reduce network traffic and user load on the Back End as well as speeding up the database reponse times.

So, where do more experienced developers put their tables and is it wise to have the "fixed data" held in the Front End? Are there any significant benefits from such a scheme?

BTW, I have an automatic update utility built in to the DB (thanks to this forum). As and when the Administrator updates the fixed data, the release number can be updated and all users will have the latest version next time they log in.

Any thoughts appreciated. Regards,

 

[KeithG]

I would say that you want to keep all of your tables in the back end because each of your users should use a different front end. What happens if a user changes the information stored in there front end, no one else will see the changes.

 

[Keith Nichols]

I would say that you want to keep all of your tables in the back end because each of your users should use a different front end. What happens if a user changes the information stored in there front end, no one else will see the changes.

Hi Keith,

You are right in what you say. The tables that are held in the Front End are those that have fixed data that is changed very infrequently. Things like position titles, departments, category tables and the like.

This data can only be updated by the DB custodian (in this instance, also the Administrator) who can then distribute the revised front end via the Automatic Front End update system.

Regards,

 

[Calvin]

Keith,
I think you've a fair understanding of how to divide the tables up. Mostly it will depend on the specific needs of your project.
You don't have to load everything in the backend, but sometimes in the begining of a project, by having all your tables in the backend, it may be easier to update the support tables with missing/forgotten/overlooked data without having to recompile and redistribute your frontend and then later when your more comfortable that your support tables contain everything they need for the forseeable future, then move them to the frontend for better performance.
Keep in mind having the data in the front-end will make for a larger file. Also having tables in the frontend can make it inflate more and can sometimes become corrupted unless you keep it under a careful maintenace plan.
I have projects that span all aspects of need and It's just a matter of finding that balance between performance and maintenance.

 

[Pat Hartman]

All the tables need to exist in the backend because that is where referential integrity is defined and enforced. If you have lots of lookup tables that change infrequently AND you have performance issues, you can copy static tables to the fe where they will be used ONLY as the RowSources for combos. If you do this, repopulate the lookup tables each time the database is opened. That means that they're never more than a day out of date and it also gives the user an easy way to force a refresh if he knows he needs one.

 

[Keith Nichols]

All the tables need to exist in the backend because that is where referential integrity is defined and enforced.

Hi Pat,

That sounds like there could be some serious problems down the line by keeping the tables on the front end. I will move them back to the BE.

Many thanks.

 

[Pat Hartman]

I agree. The tables all belong in the back end unless you are having response issues directly related to the filling of combos.

 

[jsanders]

I agree. The tables all belong in the back end unless you are having response issues directly related to the filling of combos.

I actually put my version table in the front end.

 

[Keith Nichols]

I actually put my version table in the front end.

J,
That's the only table left in my front end.

Regards,

 

[RichMorrison]

FE fixed data

Keith,

I agree with the opinion that tables should be in the back end, for all the reasons given. But if you are experiencing network traffic problems you could consider this.

Put your read-only lookup tables in a separate back end and distribute it to the users. This lets you distribute a new version of the front end or a new version of the lookup tables when needed.

I do not know from experience if this will significantly reduce network traffic and improve performance. It's probably not hard to test.

Good Luck,
RichM