That's one of the kickers.  The 7% of the time the column is populated
is determined by business logic.  And when the business logic says it's
needed, at application run time if certain conditions were met, the
column takes on the characteristic NOT NULL attribute.

-----Original Message-----
From: Dan Shirah [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 02, 2007 3:50 PM
To: Max Thayer
Subject: Re: [PHP-DB] DB Design Concepts

I am assuming that since column b will only be populated 7% of the time
that it is not a value specific column (does not matter if it has a
value or not)
Therefore I would suggest leaving the NULL's in there as it will not (at
least should not) affect any system performance.

On 5/2/07, Max Thayer <[EMAIL PROTECTED]> wrote: 

        I'm using MySQL 5.x InnoDB engine, transactional tables.  I have
        conceptual design question.  If I have a two columns 'a' and
'b', a is 
        the primary key, and b is a type double, in table 1 (T1) for
        column b will have many NULL values, do I leave it with an allow
        constraint on the column or pull the column and place it into
table 2
        (T2) with a foreign key, making a simple optional one-to-one 
        relationship.  Over the course of time, as the table fills with
        will a column w/ many NULL values have a detrimental effect on
        performance or maintenance with regards to the DB?  Am I missing
        something here in DB design 101, by leaving the column in the T1
        knowing it will only be populated 7% of the time; what are the
        implications based on the RDBMS and engine I'm using?
        Do I go to 2nd NF simply because a column is not going to be
        as often?
        Max H. Thayer
        Lead Software Developer
        Center for High-Throughput Structural Biology
        Hauptman-Woodward Medical Research Inst.
        700 Ellicott St.
        Buffalo, NY 14203 
        Phone: 716-898-8637
        Fax: 716-898-8660

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to