Managing Large Tables

Is there a prevailing wisdom on whether or not tables with many fields
should be split into 2 or more separate tables? I have a situation with one
table, Table_1, that it seemed to make sense to split because it had so many
fields AND some data, Table_2, does not need to be maintained indefinitely.


The initial data entry requires information be stored in both tables and
synchronized with each other in a 1-to-1 relationship. After a period of
time, Table_2 data has served its purpose and is no longer needed. It is my
preference to delete the Table_2 record once the data has served its
purpose. So much for the 1-to-1.

If I stored ALL the fields only in Table_1, it seems rather clumsy as even
if I went back and deleted the time sensitive data, I would have a bunch of
unused fields. With two tables, I am concerned about the design being
equally clumsy.

Thanks for sharing your thoughts on this.


Rob B.

proper relational design is not about how many fields are in a table (though
rule of thumb is more than 25-30 fields max is quite likely not normalized),
it's about putting the correct fields in the appropriate table(s) - in other
words, storing the data relationally. recommend you read up on the
principles of relational design, then you'll be in a better position to
evaluate your table structure. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth


"Rob B." wrote in message
...
Is there a prevailing wisdom on whether or not tables with many fields
should be split into 2 or more separate tables? I have a situation with
one
table, Table_1, that it seemed to make sense to split because it had so
many
fields AND some data, Table_2, does not need to be maintained
indefinitely.


The initial data entry requires information be stored in both tables and
synchronized with each other in a 1-to-1 relationship. After a period of
time, Table_2 data has served its purpose and is no longer needed. It is
my
preference to delete the Table_2 record once the data has served its
purpose. So much for the 1-to-1.

If I stored ALL the fields only in Table_1, it seems rather clumsy as even
if I went back and deleted the time sensitive data, I would have a bunch
of
unused fields. With two tables, I am concerned about the design being
equally clumsy.

Thanks for sharing your thoughts on this.


Rob B.

Rob B. wrote:
Is there a prevailing wisdom on whether or not tables with many fields
should be split into 2 or more separate tables? I have a situation
with one table, Table_1, that it seemed to make sense to split
because it had so many fields AND some data, Table_2, does not need
to be maintained indefinitely.


The initial data entry requires information be stored in both tables
and synchronized with each other in a 1-to-1 relationship. After a
period of time, Table_2 data has served its purpose and is no longer
needed. It is my preference to delete the Table_2 record once the
data has served its purpose. So much for the 1-to-1.

If I stored ALL the fields only in Table_1, it seems rather clumsy as
even if I went back and deleted the time sensitive data, I would have
a bunch of unused fields. With two tables, I am concerned about the
design being equally clumsy.

Thanks for sharing your thoughts on this.


Rob B.

A table can have as many as 255 fields. However it is very unlikely you
will ever see a table that has anything close to that which is properly
designed (normalized) Tables are split up not to reduce the number of
fields, but to create a more logical (normalized) system.

If you can provide some more information about the data you are storing
maybe we can offer some specific suggestions.

I might also suggest reducing the number of cross posted newsgroups.

--
Joseph Meehan

Dia 's Muire duit

If I am understanding correctly, you have two groups of related data. One
group is fairly static and the other is more dynamic, but related to the
static data. The dynamic portion is time sensitive and after some point in
time, no longer needed.

If this is a correct assessment of your situation, then two tables may be
correct. However, if no new data in the dynamic part will ever be entered
until the previous group is out of date and the new data will replace all of
the old data, then one table may be correct.

As pointed out by others, it is not about the number of fields in a table,
It is about the relationship of the data elements. For example, if you have
recursive data elements, you probably want a child table. For example, one
person may have zero to many phone numbers (Home, Work, Cell, Fax, Second
LIke). Many people would put a field for each type of phone in the master
record. In reality, this is not normalized data. One person may not have
any phones. Another could have even more that you plan for. To correctly
normalize these data, a child Phone table and a Phone Type table would be
used. The Phone table would be related to the master table so you would have
any possible number of Phone numbers for an individual. The Phone Type table
would be related to the Phone table so you would know what kind of phone it
is.

That should make this perfectly clear so you can make an easy and informed
decision
--
Dave Hargis, Microsoft Access MVP


"Rob B." wrote:

Is there a prevailing wisdom on whether or not tables with many fields
should be split into 2 or more separate tables? I have a situation with one
table, Table_1, that it seemed to make sense to split because it had so many
fields AND some data, Table_2, does not need to be maintained indefinitely.


The initial data entry requires information be stored in both tables and
synchronized with each other in a 1-to-1 relationship. After a period of
time, Table_2 data has served its purpose and is no longer needed. It is my
preference to delete the Table_2 record once the data has served its
purpose. So much for the 1-to-1.

If I stored ALL the fields only in Table_1, it seems rather clumsy as even
if I went back and deleted the time sensitive data, I would have a bunch of
unused fields. With two tables, I am concerned about the design being
equally clumsy.

Thanks for sharing your thoughts on this.


Rob B.

"Rob B." wrote:

Is there a prevailing wisdom on whether or not tables with many fields
should be split into 2 or more separate tables? I have a situation with one
table, Table_1, that it seemed to make sense to split because it had so many
fields AND some data, Table_2, does not need to be maintained indefinitely.

I would never delete data. You never know when someone might want it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Actually I know. Usually within a week of you deleting the data........
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Tony Toews [MVP]" wrote:

I would never delete data. You never know when someone might want it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Joseph Meehan wrote:

Rob B. wrote:

Is there a prevailing wisdom on whether or not tables with many fields
should be split into 2 or more separate tables? I have a situation
with one table, Table_1, that it seemed to make sense to split
because it had so many fields AND some data, Table_2, does not need
to be maintained indefinitely.


The initial data entry requires information be stored in both tables
and synchronized with each other in a 1-to-1 relationship. After a
period of time, Table_2 data has served its purpose and is no longer
needed. It is my preference to delete the Table_2 record once the
data has served its purpose. So much for the 1-to-1.

If I stored ALL the fields only in Table_1, it seems rather clumsy as
even if I went back and deleted the time sensitive data, I would have
a bunch of unused fields. With two tables, I am concerned about the
design being equally clumsy.

Thanks for sharing your thoughts on this.


Rob B.


A table can have as many as 255 fields. However it is very unlikely you
will ever see a table that has anything close to that which is properly
designed (normalized) Tables are split up not to reduce the number of
fields, but to create a more logical (normalized) system.

If you can provide some more information about the data you are storing
maybe we can offer some specific suggestions.

I might also suggest reducing the number of cross posted newsgroups.

Joseph Meehan wrote:

Rob B. wrote:

Is there a prevailing wisdom on whether or not tables with many fields
should be split into 2 or more separate tables? I have a situation
with one table, Table_1, that it seemed to make sense to split
because it had so many fields AND some data, Table_2, does not need
to be maintained indefinitely.


The initial data entry requires information be stored in both tables
and synchronized with each other in a 1-to-1 relationship. After a
period of time, Table_2 data has served its purpose and is no longer
needed. It is my preference to delete the Table_2 record once the
data has served its purpose. So much for the 1-to-1.

If I stored ALL the fields only in Table_1, it seems rather clumsy as
even if I went back and deleted the time sensitive data, I would have
a bunch of unused fields. With two tables, I am concerned about the
design being equally clumsy.

Thanks for sharing your thoughts on this.


Rob B.


A table can have as many as 255 fields. However it is very unlikely you
will ever see a table that has anything close to that which is properly
designed (normalized) Tables are split up not to reduce the number of
fields, but to create a more logical (normalized) system.

If you can provide some more information about the data you are storing
maybe we can offer some specific suggestions.

I might also suggest reducing the number of cross posted newsgroups.

On Aug 2, 7:23 pm, "Tony Toews [MVP]" wrote:
I would never delete data. You never know when someone might want it.

That's what backups are for. Forcing users to include AND IsDeleted =
'N' in *every* join with *every* table will not make you popular.

Jamie.

--

Jamie Collins wrote:

I would never delete data. You never know when someone might want it.

That's what backups are for. Forcing users to include AND IsDeleted =
'N' in *every* join with *every* table will not make you popular.

Please explain that cryptic answer in a bit more detail.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/