Splitting table to reduce size

[RipperT]

Hello, all,

I am developing an additional segment to an existing split, secured
(Access2000) DB and have found that I will be adding many more fields to the
DB's main table. I would like to just create another table with the same PK
and join them 1 to1. Is it advisable to do this just because the table is
becoming unwieldy? Or is it a bad idea? I've actually already done it, but I
stopped when I found that if I create a form based on the main table with a
subform based on the new table (can that even be called parent-child?), the
subform will only accept one record because of the 1 to1 relatationship,
which is fine, but then it errors out, causing my already impatient users
unnecessary anxiety. Is there a way to prevent the form rolling over to the
next blank record? I am sailing into unfamiliar waters here. Any help will
be warmly recieved and graciously appreciated.

Cheers,

Rip

 

[Smartin]

Hello, all,

I am developing an additional segment to an existing split, secured
(Access2000) DB and have found that I will be adding many more fields to the
DB's main table. I would like to just create another table with the same PK
and join them 1 to1. Is it advisable to do this just because the table is
becoming unwieldy? Or is it a bad idea? I've actually already done it, but I
stopped when I found that if I create a form based on the main table with a
subform based on the new table (can that even be called parent-child?), the
subform will only accept one record because of the 1 to1 relatationship,
which is fine, but then it errors out, causing my already impatient users
unnecessary anxiety. Is there a way to prevent the form rolling over to the
next blank record? I am sailing into unfamiliar waters here. Any help will
be warmly recieved and graciously appreciated.

Cheers,

Rip

I do hope others chime in on this, but I can't tell you how many tables
I have seen split "because they're getting so large aren't they?" only
to find at a later date someone asks "can we bring back those archived
tables?..."

Unless you are reaching Access limits or suffering a performance hit for
having a lot of records online, my advice would be don't split the
table. Offer filtering if you need to in order to pare down the records
on display.

 

[RipperT]

I should make a distinction between splitting the table and archiving. My
meaning is that I will be adding fields to the table so that it has too many
(IMO), so I would like to create a separate table to store the additional
data and join them via a common primary key. My meaning is not to cut off
old records and store them in another table that will serve as an archive.

Ripper

 

[Jerry Whittle]

The number of fields in a table or the number of tables has nothing to do
with size (with a copule of exceptions). The database and table design needs
to be driven by proper normalization of the data. Period. Splitting off into
other tables due to adding a few more fields is like the tail wagging the
dog. Follow normalization rules to about the third normal form and let that
dictate your tables and relationships.

Exceptions: Access has two limitations that could cause you to split up a
table in something like a 1-to-1 relationship before normalization would.
(1) The 255 field limit for a table. I'd like to see a properly normalized
database having a table like that. I've seen two tables a little over 100
fields but that was rare.
(2) Access has a 2000 character limit per record excluding memo and OLE
fields. As a text field can contain up to 255 characters, you COULD run out
of record space with 7 or 8 FULL text fields. Still this is rare as usually
text fields taking up a lot of space are better off in a memo field.

 

[Tim Ferguson]

have found that I will be adding many more fields
to the DB's main table. I would like to just create another table with
the same PK and join them 1 to1. Is it advisable to do this

No. Jerry is right on this: it really sounds as though you need to be
rethinking the design of this database.

just
because the table is becoming unwieldy?

There really is no such thing as unwieldy: the db engine has methods to
do all the wielding you want. If there are too many records to look at,
then use a query to select just the Cincinatti sales; if there are too
many fields (ahem) then use a query to select just the fields of
interest.

Or is it a bad idea?

Yes -- not least because it creates horrendous problems in maintenance
and the UI. As you found out.

These are the top three rules of making a good user interface:
1. Get the database design right
2. Get the database design right
3. Get the database design right

Hope that helps


Tim F

 

[Jeff Boyce]

If you'll provide an example of the kinds of fields you already have, and
are considering adding, the 'group may be able to offer more specific
advice.

A note -- my "many more" and your "many more" may not bear any resemblance
to each other. How many?

Regards

Jeff Boyce
Microsoft Office/Access MVP