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 a
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 which
column b will have many NULL values, do I leave it with an allow null
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 records,
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 and
knowing it will only be populated 7% of the time; what are the major
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 populated
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 <> <>

Reply via email to