Design structure

[an]

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

 

[Jeff Boyce]

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

 

[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

 

[Tim Ferguson]

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

 

[an]

Many thanks for your explanation.
an

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


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

.

 

[John Vinson]

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]

 

[Chris2]

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.

 

[an]

Sorry for my delay.
Thanks for your reply.
an

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


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]

Sorry for my delay.

Many thanks for your help too.
an