Same database or another?

[accesskastle]

Hi folks. I need help conceptually with a problem. I've recently
constructed database for monitoring signs of feral animals- fourteen tables,
expected to have about 100,000 records in at least 2 of those tables, 100's
to 1000's of records in the others by the end of about 10 years. I have not
yet split it or put it on the network; I have a few more issues to handle,
but that's eventually where it would go.

I've recently been asked to add a new section to the database that looks at
work toward the capture of the feral animals. The reason I think the person
wanted it in one mdb was because it was later going to link to something like
ArcGIS, and I think they feel it would be easier to link to one database than
two.

Making the new additions would not add more than another 13 tables, which I
estimate will be about 15000 records max in what will probably be the one
table, maybe max of 500 to 1500 in the others. However, I'm worried about
adding on the new tables, queries, forms, etc for a couple of reasons:

1)The data tables are not really related (maybe loosely by dates), except
maybe by two tables which are only used for lookup. The Performance Analyzer
appears to imply that if tables are unrelated that Access spends extra time
searching tables to attempt a relation.

2)Having to sift through lookups tables as part of the old database for the
new tables that would be added will likely be a chore for the person doing
capture information. Capture lookups will likely only require a very small
set of the lookup tables. I know its not good practice, but to be honest,
it's unlikely that we'd need to try query the old lookups against the
captures to find duplicates if it were two separate databases.

3)The captures part would be accessed a whole lot more frequently than the
monitoring.

4)Decreased security.

4)Increased likelihood of corruption

5)The whole thing would be slower

Are my concerns valid? What are everyone's thoughts on this? I'm using
Access 2002 mdb's, Jet only, going to split it and put it on our LAN.

accesskastle

 

[Jeff Boyce]

I've not often seen a database described in terms of number of tables and
number of records. In a relational database like Access, it is much more
common to discuss the entities and relationships, and I'm not very clear on
these in your situation.

From your description, it sounds like you have users entering directly into
tables. This is not a good idea, especially if you've designed a
well-normalized data structure. In Access, tables store data, forms display
it (and serve for data entry/edit).

(see additional comments in-line below)

Hi folks. I need help conceptually with a problem. I've recently
constructed database for monitoring signs of feral animals- fourteen tables,
expected to have about 100,000 records in at least 2 of those tables, 100's
to 1000's of records in the others by the end of about 10 years. I have not
yet split it or put it on the network; I have a few more issues to handle,
but that's eventually where it would go.

I've recently been asked to add a new section to the database that looks at
work toward the capture of the feral animals. The reason I think the person
wanted it in one mdb was because it was later going to link to something like
ArcGIS, and I think they feel it would be easier to link to one database than
two.

You could create a new Access database to handle this, then link to the
tables from within your current application.

Making the new additions would not add more than another 13 tables, which I
estimate will be about 15000 records max in what will probably be the one
table, maybe max of 500 to 1500 in the others. However, I'm worried about
adding on the new tables, queries, forms, etc for a couple of reasons:

1)The data tables are not really related (maybe loosely by dates), except
maybe by two tables which are only used for lookup. The Performance Analyzer
appears to imply that if tables are unrelated that Access spends extra time
searching tables to attempt a relation.

You are saying that there is no relationship between what you are currently
storing and what new data is being asked for?

2)Having to sift through lookups tables as part of the old database for the
new tables that would be added will likely be a chore for the person doing
capture information. Capture lookups will likely only require a very small
set of the lookup tables. I know its not good practice, but to be honest,
it's unlikely that we'd need to try query the old lookups against the
captures to find duplicates if it were two separate databases.

See my comment above - no one should be "sifting through" tables. The whole
purpose of lookup tables, as defined in Access, is that they work
automatically to provide meaningful values, rather than ID#s. These should
ONLY be referenced via your forms and queries. "Step away from the
tables..."

3)The captures part would be accessed a whole lot more frequently than the
monitoring.

This sounds like you are saying that some tables would be used more often
than others... so?!

4)Decreased security.
How?


4)Increased likelihood of corruption

Again, how?

5)The whole thing would be slower

Why? On what do you base this assertion? A well-normalized Access
database, with appropriate indexing, is generally as fast as you need it to
be. If your database reaches the point where it cannot hold all the data
any longer, there are database products with more horsepower (!and cost!).

 

[accesskastle]

Hi Jeff- Thanks for your reply. That's a negative to entry into tables or
that I have used the lookup wizard provided. I currently use forms for data
entry and my tables are normalized.

I was trying to express that it is impossible to create any key linking the
old data to what I have been asked to add on because conceptually, there is
no relationship between the two.

What I was expressing is that the performance analyzer seems to imply that
if I choose to add tables to database that have absolutely no relation to
other tables in the database, then that costs performance and time in
execution because Access tries to search table objects to guess relations.
Is this true?

The data tables may be linked in that there might be similar tables that are
used purely for lookup. Not lookup wizard. I mean that on a form, there is
a combo box whose recordsource is a query, which is based on a single table
that usually has one to two fields. There will be conceivably thousands of
names in this combo box which are wholely unnecessary for the person who will
have to enter the new capture data.

What I'm asking myself is why even bother filtering if most of the data is
superflous to someone entering the new data (with two databases)?

Security- More people accessing.

Corruption- More data. Unrelated data. Increased database use.

accesskastle