Design structure

Hello!

The question could not be pacific, but I would like an
indication, in case that it were linear.

To manage 100 records in Access (without forms), what it
will be preferable: One table and several queries or some
related tables and few queries?
E to manage 100,000, please.

And with Forms, please.

Anticipatedly grateful.
an

It seems from your question that you are asking for general help creating an
Access database. These Access newsgroups seem to focus more on helping
folks answer specific questions, rather than providing general assistance.

You'll find a strong consensus that users work with Forms, not directly with
Tables.

Since Access is a relational database, your question about how many tables
is premature. Until you have come up with a model of your data entities and
relationships, the question of tables is moot.

If you are asking if Access can manage a table with 100,000 rows, that would
depend on how "long" the rows were.

I'm sorry, but I do not understand your opening sentence

The question could not be pacific, but I would like an
indication, in case that it were linear.

Could you re-phrase it?

Good Luck

Jeff Boyce
Access MVP


To manage 100 records in Access (without forms), what it
will be preferable: One table and several queries or some
related tables and few queries?
E to manage 100,000, please.

And with Forms, please.

Anticipatedly grateful.
an

Thanks for your reply.

Basically, I would like what the best performance of the
Access:
If with one tbale with many fields, or if with the fields
distributed for some tables and related between itself.

Thanks.
an

-----Original Message-----
It seems from your question that you are asking for
general help creating an
Access database. These Access newsgroups seem to focus
more on helping
folks answer specific questions, rather than providing
general assistance.

You'll find a strong consensus that users work with
Forms, not directly with
Tables.

Since Access is a relational database, your question
about how many tables
is premature. Until you have come up with a model of
your data entities and
relationships, the question of tables is moot.

If you are asking if Access can manage a table with
100,000 rows, that would
depend on how "long" the rows were.

I'm sorry, but I do not understand your opening sentence

The question could not be pacific, but I would like an
indication, in case that it were linear.

Could you re-phrase it?

Good Luck

Jeff Boyce
Access MVP


To manage 100 records in Access (without forms), what it
will be preferable: One table and several queries or
some
related tables and few queries?
E to manage 100,000, please.

And with Forms, please.

Anticipatedly grateful.
an

.

"an" wrote in news:186d01c50533
:

Basically, I would like what the best performance of the
Access:
If with one tbale with many fields, or if with the fields
distributed for some tables and related between itself.

The decision to use a database project is not about performance (by
which I gather you mean speed) but about accuracy and integrity. There
is no point in having the fastest system in the world if it's going to
lie about your data.

Therefore correct design comes first -- and the choices about that are
dictated by the real-world entities that you are modelling -- and
faffing about with timings and processing speed is a long way down the
list.

Do you know the fastest square-root routine in the world?

Public Function FastSquareRoot( AValue as Double ) As Double
FastSquareRoot = 2.4593743
End Function

It's not always very accurate, but you should just check out those
benchmarks!

HTH


Tim F

Many thanks for your explanation.
an

-----Original Message-----
"an" wrote in
news:186d01c50533
:

Basically, I would like what the best performance of
the
Access:
If with one tbale with many fields, or if with the
fields
distributed for some tables and related between itself.

The decision to use a database project is not about
performance (by
which I gather you mean speed) but about accuracy and
integrity. There
is no point in having the fastest system in the world if
it's going to
lie about your data.

Therefore correct design comes first -- and the choices
about that are
dictated by the real-world entities that you are
modelling -- and
faffing about with timings and processing speed is a long
way down the
list.

Do you know the fastest square-root routine in the world?

Public Function FastSquareRoot( AValue as Double ) As
Double
FastSquareRoot = 2.4593743
End Function

It's not always very accurate, but you should just check
out those
benchmarks!

HTH


Tim F

.

On Fri, 28 Jan 2005 02:10:12 -0800, "an"
wrote:

Hello!

The question could not be pacific, but I would like an
indication, in case that it were linear.

To manage 100 records in Access (without forms), what it
will be preferable: One table and several queries or some
related tables and few queries?
E to manage 100,000, please.

And with Forms, please.

As indicated in this thread - that's the wrong question.

Your Table structure should reflect the logical structure of the data.
If the data is - based on the real-life structure of the information -
a single table, then use a single table; if it's 63 tables with
multiple relationships, then use that structure.

It's possibly worthwhile to - CAREFULLY, judiciously, and very
hesitantly! - "denormalize" a database. However this should be done
ONLY if you have built the normalized database and *demonstrated* that
a properly optimized database, running on appropriate hardware and
network, cannot provide sufficient performance.

Just as a note, for 100 records it makes no difference at all - you'll
get instant performance in a table, a form, queries, or whatever.
100,000 records will require proper indexing and decent query design
but should still pose no problems for performance. 10,000,000 records
will take a fair bit more work but is still well within Access'
capabilities.

John W. Vinson[MVP]

"an" wrote in message
...
Thanks for your reply.

Basically, I would like what the best performance of the
Access:
If with one tbale with many fields, or if with the fields
distributed for some tables and related between itself.

Thanks.
an


The performance of a relational database is based on *many* factors.

The two main activities that going to happen in any database on are
either SELECT statements (getting data), or action queries (UPDATE,
INSERT, DELETE).

Indexes on tables help SELECT statements find data more easily, and
run faster.

Indexes on tables (the more you have the worse it is), slow down
action queries (the table is changed, and so are all the indexes).

So, Indexes have conflicting value, based on what you're going to be
doing.

"Wide" tables, those with many columns (fields), are generally slower
to process when they become large, because there is a lot of data to
move around. In a properly normalized database, you shouldn't have
many "wide" tables, preferably, none.


The design of the tables can have a much larger effect on performance
that just the size.

If the tables aren't properly normalized, running SELECT queries to
ask many types of questions becomes difficult to impossible.
Work-arounds must be used, and these work-arounds inevitably are ugly,
nasty, and above all, slow.


For more help:

Google the following:

"relational database design"
"database normalization"
"1NF 2NF 3NF BCNF 4NF"

And: www.mvps.org/access/ is a repository of much Access goodness, and
a jumping off point to much more as well.


Sincerely,

Chris O.

Sorry for my delay.
Thanks for your reply.
an

-----Original Message-----
On Fri, 28 Jan 2005 02:10:12 -0800, "an"
wrote:

Hello!

The question could not be pacific, but I would like an
indication, in case that it were linear.

To manage 100 records in Access (without forms), what it
will be preferable: One table and several queries or
some
related tables and few queries?
E to manage 100,000, please.

And with Forms, please.

As indicated in this thread - that's the wrong question.

Your Table structure should reflect the logical structure
of the data.
If the data is - based on the real-life structure of the
information -
a single table, then use a single table; if it's 63
tables with
multiple relationships, then use that structure.

It's possibly worthwhile to - CAREFULLY, judiciously, and
very
hesitantly! - "denormalize" a database. However this
should be done
ONLY if you have built the normalized database and
*demonstrated* that
a properly optimized database, running on appropriate
hardware and
network, cannot provide sufficient performance.

Just as a note, for 100 records it makes no difference at
all - you'll
get instant performance in a table, a form, queries, or
whatever.
100,000 records will require proper indexing and decent
query design
but should still pose no problems for performance.
10,000,000 records
will take a fair bit more work but is still well within
Access'
capabilities.

John W. Vinson[MVP]
.

Sorry for my delay.

Many thanks for your help too.
an

-----Original Message-----

"an" wrote in
message
...
Thanks for your reply.

Basically, I would like what the best performance of the
Access:
If with one tbale with many fields, or if with the
fields
distributed for some tables and related between itself.

Thanks.
an


The performance of a relational database is based on
*many* factors.

The two main activities that going to happen in any
database on are
either SELECT statements (getting data), or action
queries (UPDATE,
INSERT, DELETE).

Indexes on tables help SELECT statements find data more
easily, and
run faster.

Indexes on tables (the more you have the worse it is),
slow down
action queries (the table is changed, and so are all the
indexes).

So, Indexes have conflicting value, based on what you're
going to be
doing.

"Wide" tables, those with many columns (fields), are
generally slower
to process when they become large, because there is a lot
of data to
move around. In a properly normalized database, you
shouldn't have
many "wide" tables, preferably, none.


The design of the tables can have a much larger effect on
performance
that just the size.

If the tables aren't properly normalized, running SELECT
queries to
ask many types of questions becomes difficult to
impossible.
Work-arounds must be used, and these work-arounds
inevitably are ugly,
nasty, and above all, slow.


For more help:

Google the following:

"relational database design"
"database normalization"
"1NF 2NF 3NF BCNF 4NF"

And: www.mvps.org/access/ is a repository of much Access
goodness, and
a jumping off point to much more as well.


Sincerely,

Chris O.


.