Managing Large Tables

[Rob B.]

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.

 

[tina]

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

 

[Joseph Meehan]

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.

 

[Klatuu]

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

 

[Tony Toews [MVP]]

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/

 

[Jerry Whittle]

Actually I know. Usually within a week of you deleting the data........

 

[John Smith]

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.

 

[John Smith]

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.

 

[Jamie Collins]

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.

--

 

[Rob B.]

Normally, I would not delete data, but of course, everyone's needs are a bit
different. It's data that is sensitive and doesn't need to be kept once it
has fulfilled its purpose. My "concern" for designing a good database was
the table would be carrying a lot of empty 255 character text fields if I
stuck the sensitive field in with the main table.

In any case, thanks to all who dropped a response. My basic question was
answered about whether or not there's prevailing wisdom on splitting tables
with a large number of fields into separate tables.

"It depends."


Rob B.

 

[Tony Toews [MVP]]

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/

 

[Tony Toews [MVP]]

Normally, I would not delete data, but of course, everyone's needs are a bit
different. It's data that is sensitive and doesn't need to be kept once it
has fulfilled its purpose.

Ok, if it's sensitive then that's a bit different. For example it's
my understanding that the credit card companies demand you delete the
ccv data on credit card transactions after 48 hours..

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/

 

[Jamie Collins]

Please explain that cryptic answer in a bit more detail.

That would be my pleasure.

You said, "I would never delete data." What design pattern can we
infer about your tables if you never remove any rows from them?

To assume your applications are never successful enough for any of
their data to reach obsolescence would be unkind <g>. All your tables
could be valid-time state ('history') tables -- start- and end date
pair on each row, sequenced primary keys via table-level CHECK
constraints, etc -- with VIEWs to return only rows in the current
state (end_date IS NULL or similar 'magic date' value). Maybe a
similar transaction log table design with summary VIEWs. Most people
conclude that temporal models are overkill for most tables unless
there is a specific business need (I assumer you get authorization
from your clients before implementing your 'never deleted data'
pattern) and anyhow too complex for Access/Jet's SQL syntax e.g. you
are limited one SQL statement per PROC whereas a sequenced update
requires five statements (two INSERTs and three UPDATES).

The most common pattern I see is to add a column (is_deleted, Status,
etc) and this is the pattern I was alluding too (sorry if I was being
obscure). Again, you could provide summary VIEWs (WHERE is_deleted =
'N') and take steps to point users at these VIEWs (e.g. revoke read
permissions from the base tables while providing WITH ACCESSOWNER
OPTION stored procs for CRUD operations or only writing updatable
VIEWs LOL!) but again the typical response is to take no action and
let users figure out for themselves that they have to eliminate
notionally (but not physically) 'deleted' rows in every join condition
and search condition.

This pattern poses a problem for keys. You may want to reuse a natural
key value that has been deleted (as distinct from 'un-deleting' the
original entity with that key value). You may think that adding the
is_deleted column to the key will help but this means you will only be
allowed one key value in each state but what you actually want is an
unlimited number of entities in the is_deleted = 'Y' state sharing a
key value.

In the same spirit of sharing, if you never delete data then what
design patterns to you actually use to handle this? TIA.

Jamie.

--