Add fields vs. Join new table

Is there any sort of rule of thumb for this situation?
I have a fairly large table in my database (currently 21 fields with
over 800,000 records and growing) that is selected from, inserted to and
updated fairly often. I am adding some new features and cant decide if
it would be more efficient to add 2 fields to this table (of which 97%
of records would be null in these 2 fields) or create a new table that
would need 4 fields but only need records for the 3% and join to 1st
table for queries and reports.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!>> Is there any sort of rule of thumb for this situation?
Yes, determine whether these potential columns are really attributes of the
entity type, which the table represents. Based on your narrative, since you
have a mostly NULLs, it is reasonable to consider them as inapplicable
attributes. So they must be moved to another table. This would give you the
logical solution.
Btw, in some cases certain designers trivialize logical consequences and pay
more attention to space and performance related considerations. The myopic
justification is such cases is generally focused on the reduction in the
number of joins as an aid in performance improvements.
Anith|||I think you should have a separate table for those fields and join the 1st
table for reports and queries.
"Debbie" wrote:

> Is there any sort of rule of thumb for this situation?
> I have a fairly large table in my database (currently 21 fields with
> over 800,000 records and growing) that is selected from, inserted to and
> updated fairly often. I am adding some new features and cant decide if
> it would be more efficient to add 2 fields to this table (of which 97%
> of records would be null in these 2 fields) or create a new table that
> would need 4 fields but only need records for the 3% and join to 1st
> table for queries and reports.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>