Re: Same database or another?
- From: accesskastle <accesskastle@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 11 Apr 2005 11:29:02 -0700
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
"Jeff Boyce" wrote:
> 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)
>
> "accesskastle" <accesskastle@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:229A4EFD-B8F2-4D2A-B11D-590BB7D317CC@xxxxxxxxxxxxxxxx
> > 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!).
>
> >
> > 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
>
>
> --
> Good luck
>
> Jeff Boyce
> <Access MVP>
>
>
.
- References:
- Same database or another?
- From: accesskastle
- Re: Same database or another?
- From: Jeff Boyce
- Same database or another?
- Prev by Date: Help with table relationships
- Next by Date: Re: Table Desing Question
- Previous by thread: Re: Same database or another?
- Next by thread: Relationship Problems
- Index(es):
Relevant Pages
|
