Linked tables or Local whats best?

[Bob Quintal]

Hi
I have a general question thats about database design realy.
I have a database with a front and backend with linked tables.

The database has a table in the backend with lots of defaults
setout in fields my question is this.
will the network and databse be faster if on startup of the front
end I copy the default table from the back end to the front end
and not have the front end linked to the default table.
Im thinking that every operator with a front end open will
constantly be pulling default information to populate forms such
as sales tax and company address etc.
the defaults do not change often ( maybe once a year ) and maybe I
could flag if any changes have been made and allow an update of
the table should a change be made. Record locking could be a
concern if all operators are pulling from the same record.
Im intrested to know what others think about this.

Reading of a table should not invoke any locking. The locking
mechanism only starts when a user begins an edit on a table. Since
you say you only edit the table "maybe once a year", moving the
table will have no impact.

Moving the lookup table to the front end, with the other tables
still in the back end, creates issues with relational integrity,
because cascading updates and deletes cannot be enforced easily, if
at all.

Populating comboboxes should only occur when opening the form,
unless the combobox is filtered based on some criteria that is
dependent on the current record being edited, so the traffic won't
be as high as on your main tables.

Proper indexing of the data in the lookup is the best way of
optimizing for speed.

 

[Steve]

Hi
I have a general question thats about database design realy.
I have a database with a front and backend with linked tables.

The database has a table in the backend with lots of defaults setout in fields
my question is this.
will the network and databse be faster if on startup of the front end I copy
the default table from the back end to the front end and not have the front
end linked to the default table.
Im thinking that every operator with a front end open will constantly be
pulling default information to populate forms such as sales tax and company
address etc.
the defaults do not change often ( maybe once a year ) and maybe I could
flag if any changes have been made and allow an update of the table should a
change be made. Record locking could be a concern if all operators are
pulling from the same record.
Im intrested to know what others think about this.

 

[Arvin Meyer [MVP]]

From a logical standpoint, your idea makes sense, but there's something else
to consider. One of the ways to speed up a database on a slower network is
to maintain a constant connection to a table. By opening the table and
minimizing it, you may actually make your other connections faster.

 

[Baz]

Hi there,

How much difference it might make depends on how many users, what they are
doing etc., but it's certainly a reasonable idea to have a local copy of
data which is mostly static.

If, as you describe, you copy the table from the back end to the front end
on startup, occasional changes to the data would not be that big a deal,
users would get the updated data next time they started the system (and, if
it's importatnt, you could tell them all to restart the system, if it will
not happen often).

So, it's a good idea in principle, although it's hard to say how much
difference it would make in your environment.

 

[Jerry Whittle]

I could see a good case for doing it if the default data was initially stored
in the FE all the time. The copying over the data from the BE to the FE would
take some time just when the users are most impatient: during database
startup.

 

[Steve]

Thanks guys I think I will do some testing

 

[Steve]

Thanks again guys I have chosen to instal a table with the same fields in the
front end and just check for changes to the original table on the back end on
startup
regards
Steve - from a land down under