Purist or Pragmatist

Hi Group,

In the context of normalization. If I understand it correctly.

I have a table with ten fields. One of those fields only receives data in
about 5% of the records. I should move that field to its own table. ??

I have 6 tables with any number of fields. One or more of those fields in
any one of those tables only receives data in about 5% of the records. I
should move each of the offending fields in each table to its own table. ??

Would it be practical to make one table that contains all of the offending
fields?

As I think about it. Probably not. The sixth table isn't connected to the
first table.

pk1 - fk1/pk2 -fk2/pk3 - fk3

My application is pretty small. 6 tables and about 16,000 records in 5
years.

By way of explanation. I have so many rarely populated fields because I have
tried to cover every possible senario. Even the ones that only occur once in
a year.

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike

In my OPINION there is really no need to move the data to a separate
field based on percentage of use. There may be other reasons to do so,
but a simple percentage of use is not a good reason.



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


Mike Revis wrote:
Hi Group,

In the context of normalization. If I understand it correctly.

I have a table with ten fields. One of those fields only receives data in
about 5% of the records. I should move that field to its own table. ??

I have 6 tables with any number of fields. One or more of those fields in
any one of those tables only receives data in about 5% of the records. I
should move each of the offending fields in each table to its own table. ??

Would it be practical to make one table that contains all of the offending
fields?

As I think about it. Probably not. The sixth table isn't connected to the
first table.

pk1 - fk1/pk2 -fk2/pk3 - fk3

My application is pretty small. 6 tables and about 16,000 records in 5
years.

By way of explanation. I have so many rarely populated fields because I have
tried to cover every possible senario. Even the ones that only occur once in
a year.

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike

You don't have enough records to really worry about it. If you had 160,000
records instead of 16,000 you might well think about a table with fields
which are rarely used, or used in a specific case. In no case do you ever
want to put multiple dissimilar data in the same table. Remember a table
describes a specific data object and a field describes data within the
table.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Mike Revis" wrote in message
...
Hi Group,

In the context of normalization. If I understand it correctly.

I have a table with ten fields. One of those fields only receives data in
about 5% of the records. I should move that field to its own table. ??

I have 6 tables with any number of fields. One or more of those fields in
any one of those tables only receives data in about 5% of the records. I
should move each of the offending fields in each table to its own table.
??

Would it be practical to make one table that contains all of the offending
fields?

As I think about it. Probably not. The sixth table isn't connected to the
first table.

pk1 - fk1/pk2 -fk2/pk3 - fk3

My application is pretty small. 6 tables and about 16,000 records in 5
years.

By way of explanation. I have so many rarely populated fields because I
have tried to cover every possible senario. Even the ones that only occur
once in a year.

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike

We're always on the lookout for non-normalization, but I think a lot of
people go in the opposite direction and over-normalize, which is what you'd
really be doing by moving a field to a separate table simply because it's
only used in X % of records.

The general rule I use about a given field is will it have a finite number of
values or an undetermined number of values for a given ID.

Say you have an organization for professionals in a given field.

A given professional can have one and only one membership in the organization,
so I'd keep the field holding his membership number in the table with his
name, DOB, mailing address, etc.

But say that the organization offers 10 publications, with some members
subscribing to one, some to two or three and some to all of them. Since you
don't know how many a given member will subscribe to, I'd move publications
to its own table, and relate it to the first table thru the membership number.


--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

"Mike Revis" wrote:

By way of explanation. I have so many rarely populated fields because I have
tried to cover every possible senario. Even the ones that only occur once in
a year.

That's fine. I have lots of tables with fields that are only
occasionally used. Including a memo field for whatever doesn't fit
anywhere else.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

Many thanks to everyone. Now I can stop stressing and get back to work.

Best regards,
Mike

"Tony Toews [MVP]" wrote in message
...
"Mike Revis" wrote:

By way of explanation. I have so many rarely populated fields because I
have
tried to cover every possible senario. Even the ones that only occur once
in
a year.

That's fine. I have lots of tables with fields that are only
occasionally used. Including a memo field for whatever doesn't fit
anywhere else.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/