Splitting database

[Brian]

I have what is probably a stupid question...

I have a database used by several people in our office. I
split the database into a front-end/back-end using the
database split tool to improve access.

Previously, the single file database resided on our
server. Having split the database, the back-end file
containing the data tables should reside on the server,
but I'm not sure how I should handle the front end piece.
I'd like to keep it on the server, because then I have
only one central file to edit/update when changes need to
be made. But that doesn't seem like it will add much
improvement to the access time for people using the
database. If the front end piece resides on users'
desktops, I have to worry about updating it on each
computer each time I make changes.

If someone can just spell out the benefits/procedure for
splitting databases, it would be helpful. Most of what
I've read has not been very helpful.

-Thanks

 

[Allen Browne]

Everyone should have their own copy of the front end.

Think of the front end as the application (the program), and the back end as
the data. You install Word onto the user's computer? Do the same with your
program.

You could put a copy of the front end in each user's space on the server if
you wish. This means that everyone has their own, independent copy of the
front end, so it solves the concurrency issues, and makes it possible for
you to modify the objects on your copy while the others are using theirs.
Naturally performance won't be as good as a local front end, and the network
traffic will be significantly higher if that's an issue.

 

[Brian]

Ok, to muddy the waters now...
What if multiple front end users are developing
queries/reports on their own that multiple users
potentially want to use. Because multiple users (we're
talking just a few - maybe 3 - it's a small office) are
currently sharing the same front end, any
changes/additions to reports, etc. are shared. If I put a
separate front-end copy on each desktop, I either have to:
1) take over all report/query writing responsibilities
myself, or 2) somehow create a system where I periodically
combine updates and distribute fresh copies.

The second option is highly undesireable and prone to
serious problems. The first option may actually be
worthwhile, as most front end users have a tendency to
clutter the database with multiple, poorly written queries
and reports ad nauseum.

-Brian

 

[Allen Browne]

Brian, your two options could both be useful in different settings.

To assign all updates to one person is the simpler option, esp. if the
others don't know what they are doing.

Assigning parts of the task to different developers is quite viable, but
requires better organisation: agreed goals, demarcation, naming conventions,
reusable code available, etc.