DB Rule of thumb

[azu_daioh]

Im about to create a huge database and would like to know if there are
any restrictions I should consider. ex. how many fields in one table?
how many tables in one database? How many subforms in one form? etc.

Is there a place anyone could direct me as a starting point? It would
be nice if I'm aware of any restrictions when creating a huge database
before starting one. Right now, I'm doing normalization -- to figure
out the tables, fields, etc I need and where they should be stored.
And, one of the tables has more than 40 fields. I'm trying to group
related fields together into tables but some of the fields are
unrelated to more than 1table.

Thank you,

Sharon

 

[John W. Vinson]

Im about to create a huge database and would like to know if there are
any restrictions I should consider. ex. how many fields in one table?
how many tables in one database? How many subforms in one form? etc.

See Help for "Specifications", at least for starters. Short answer - 255
fields max in a table is the formal limit, but over 30 is VERY suspicious; no
meaningful limit on number of tables (hundreds in some databases I've worked
with); the limit on subforms is mainly dictated by performance and usability,
not sytem limits.

Is there a place anyone could direct me as a starting point? It would
be nice if I'm aware of any restrictions when creating a huge database
before starting one. Right now, I'm doing normalization -- to figure
out the tables, fields, etc I need and where they should be stored.
And, one of the tables has more than 40 fields. I'm trying to group
related fields together into tables but some of the fields are
unrelated to more than 1table.

The grouping should be based on logical relationships in the real-world data.
In your normalization process you'll identify types of Entities - real-life
people, things or events. Each kind of Entity gets its own table. Each Entity
has its own set of "attributes" - distinct nonrepeating chunks of information
that you need to record for each instance of that type of entity. Some
entities have only one or two attributes (e.g. a States table would probably
have two fields, StateCode and StateName); others might have twenty or thirty
attributes. A table should have as many fields as are necessary to store the
information about that entity - no more and no fewer!

For some pointers, see

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]

 

[Joseph Meehan]

Huge is a relative term. Most of the time when it is used, it really
does not mean that. Access depending on the version is limited to one or
more gigs in size. Very databases described as huge come close to that.

John did a good job of explaining normalization and I can only stress
that it is very very important and that it is far easier to do now before
you start designing a database than after you are half way through it.

Just because Access can handle 255 fields in a table is not a good
reason to use that many. Modern database design is a totally different
animal than spreadsheet design and it sometimes takes a while to get the
idea.

I would suggest that you are very unlikely to come close to any of the
limitations of Access as long as you use proper normalization.

 

[azu_daioh]

Thank you John and Joseph. It really helps. I will start reading on
the sites John provided.
I see your point Joseph. I said 'huge' because it's larger than any
database I've created. The current database is in FMPro -- containing
14 tables/forms with no relationships. So the user (us) has to enter
the same info over and over again. I'm re-creating this database
into Access. But since I will be doing normalization -- I know I will
end up with more than 14 tables.

Thanks again.

Sharon

 

[azu_daioh]

Another question. I'm unsure how to take this. I read in one of the
resource sites listed above -- that we should avoid using lookup table
in table level. What does it mean? Does it mean when I'm in table
design view - i should use lookup wizard in data type?

But if I'm in form design view, I could set the object's control
source to use the data in one of the existing table for the combo box?

Just want to make sure I'm understanding this right.

Thank you,
Sharon

 

[John W. Vinson]

Another question. I'm unsure how to take this. I read in one of the
resource sites listed above -- that we should avoid using lookup table
in table level. What does it mean? Does it mean when I'm in table
design view - i should use lookup wizard in data type?

Most folks here would say you should NOT use the lookup wizard at all. See
http://www.mvps.org/access/lookupfields.htm for a detailed critique.

But if I'm in form design view, I could set the object's control
source to use the data in one of the existing table for the combo box?

Well... you can create a combo box in form design view; its Control Source
would be the table field in which you wish to store a value (typically a
numeric key); its RowSource would be a query selecting records from the lookup
table; and its Column Count and Column Widths properties would be set to
conceal the numeric key being stored while displaying a meaningful text value
to the user.

Using the Lookup Wizard in the table makes this process a couple of
mouseclicks easier. You pay for this small advantage with all of the
disadvantages listed on the website above.

John W. Vinson [MVP]

 

[azu_daioh]

Most folks here would say you should NOT use the lookup wizard at all. Seehttp://www.mvps.org/access/lookupfields.htmfor a detailed critique.

Thanks, I meant to say -- I should 'not' use lookup wizard in data
type?

Well... you can create a combo box in form design view; its Control Source
would be the table field in which you wish to store a value (typically a
numeric key); its RowSource would be a query selecting records from the lookup
table; and its Column Count and Column Widths properties would be set to
conceal the numeric key being stored while displaying a meaningful text value
to the user.

Using the Lookup Wizard in the table makes this process a couple of
mouseclicks easier. You pay for this small advantage with all of the
disadvantages listed on the website above.

Yes, that's what I read and I think it could be the reason why one of
our databases is slow. I'll have to correct this problem. I didnt
know establishing the 'lookup' from table design view has
disadvantages than doing it in the form design view.

Many thanks again.

 

[(PeteCresswell)]

Per (e-mail address removed):

any restrictions I should consider.

I'd do my best to avoid cosmetic graphics on forms or reports. My
experience is that they degrade performance all out of proportion
to what they contribute.