Backend Theories and Practices

[twinnyfo]

Friends,

I have a question on the various theories behind the backend data in a database. Of course, I think this is the proper way to set up an Access DB, but my question has to do with implementation theories and various practices.

Here is what I have: I have one front end that all users have access to. Thanks to others on this site, everytime the user wants to access the DB, a fresh copy of the FE is copied to their local drive and the DB is opened from there. That FE actually has five Back Ends which hold various sets of data.

One BE is data downloaded from a mainframe, and that data is never updated or changed by the user, but is occasionally referred by the user. This BE is also about 150 MB.

There are small BEs, which are only accessed several times throughout the year and they save statistical and archived information. Although very small, because of their function, I have have split them out separately, so that I can go to a specific place to find specific information.

There are two other BEs, both of which are accessed and updated on a daily basis, but, again, have completley different purposes, although the data are related to each other. I've done a pretty good job (in my opinion) to keep redundancy to an absolute minimum, which sites like this have helped me to understand DB normalization and table creation theory.

I have one FE, because the FE determines who has logged in (based on their user name) and assigns various access to the user based on who they are. However, occasionally, a user may change their responsibliities, so we all access one FE, with the ability of changing roles, and thus having access to the different data sets and functions within the DB.

My question is whether this type of BE practice is a good one? Because I make frequent backups of my data, having everything together in a 200 MB BE, doesn't seem reasonable, as the main frame data does not need to be backed up (we can always acces it).

Or, is it wise to split out the BEs even more? I.e., for all the tables I use to populate the drop downs in my other tables, should those be stored separately--so that the only data in the main BE are the tables which are actually updated?

Of course, this may just be a question of preference for some people, but I thought I would ask whether anyone has any theories that would recommend one way or the other.

I appreciate any thoughts or comments you may have on this issue. As I always seek to learn and improve my skills, this is one more way I'd like to seek improvement.

Warm regards, and thanks in advance!

[TheSmileyCoder]

It can make sense to split up the database backend to some degree. If you have a form open, based on Table A in backend A, and then need to perform a operation on Table B in backend B, you should be aware that Access needs to get a hold of the file. This involves sending a request to the network server to see if you have read rights on the file in question, and whether you have rights to create a .ldb file in the folder. Note that this connection is established each time you open a table in a backend in which you did not have a previously active connection.

That CAN be a reason to not split it up as much as you have, but it depends on how your specific setup is. If the opening of table B in backend is only something the user does once a hour, then sub-optimal performance might be acceptable.

[twinnyfo]

Thanks, Smiley! That's some goo dinformation to know. Fortunately, BE C and BE D only get accessed once in a while, mostly by me, so those connections are very infrequent. However, you are kind of confirming the overall plan that I have right now, and so, I am leaning very strongly now to not split the BE any further--because of the connections required and potential slowdown.

Thanks for the advice!

[NeoPa]

I think I am in tune with Smiley when I say that you should have sound reasons for splitting up a BE if you don't have to. I cannot say that it's never a good idea, but for reasons which have already been explained it shouldn't be done without good reason.

[TheSmileyCoder]

I think If I had to list the top rules of design it would be: in priority:

1. Normalize your data
2. KISS: Keep It Simple (Stupid)

As far as I know, access is quite efficient at getting information even from large files, if its indexed. Access does not "retrive" or load the entire backend file, unless asked too.

Imagine you have table A in backend A containing 1.000.000 records. That doesn't affect table b in backend A. Getting table B is not slower, just because the backend may be a huge file.

[NeoPa]

Nor do you get any parallel processing benefits of multiple BEs as you might if they were actual servers, as the BE processing is all done on the FE processor. If anything, you just get the extra overheads of opening more files.

[TheSmileyCoder]

I think if you however query a table with joins to a table in a seperate backend, that you might get alot of extra network traffic. I must admit that we are now on the border of my understanding of how access works in regards to data transfer from backends.

[NeoPa]

Not necessarily Smiley. All the work for Access BEs is done on the local processor. This may involve network traffic if the file is not stored locally, but that needn't be the case. Of course, what you say about joins going across BEs is pretty close anyway. I would expect rules between tables of the same BE would work more efficiently than those across separate ones. Some couldn't even be defined unless they are either local (Same BE) or linked (Separate BE requiring a link to a link or worse). Anyway, not a good thing to get into without good reason.