PHP DB - Re: [PHP-DB] DB Design Concepts

Author

Re: [PHP-DB] DB Design Concepts

Dan Shirah

2007-05-02, 6:57 pm

Max,

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 <mthayer@hwi.buffalo.edu> 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
>
> http://www.chtsb.org <http://www.chtsb.org/>
>
> http://www.hwi.buffalo.edu <http://www.hwi.buffalo.edu/>
>
>
>
>

Max Thayer

2007-05-02, 6:57 pm

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:mrsquash2@gmail.com]=20
Sent: Wednesday, May 02, 2007 3:50 PM
To: Max Thayer
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] DB Design Concepts

Max,
=20
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)
=20
Therefore I would suggest leaving the NULL's in there as it will not (at
least should not) affect any system performance.

=20
On 5/2/07, Max Thayer <mthayer@hwi.buffalo.edu> wrote:=20

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=20
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=20
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=20
knowing it will only be populated 7% of the time; what are the
major
implications based on the RDBMS and engine I'm using?
=09
=09
=09
Do I go to 2nd NF simply because a column is not going to be
populated
as often?
=09
=09
=09
Max H. Thayer
=09
Lead Software Developer
=09
Center for High-Throughput Structural Biology
=09
=09
=09
Hauptman-Woodward Medical Research Inst.
=09
700 Ellicott St.
=09
Buffalo, NY 14203=20
=09
Phone: 716-898-8637
=09
Fax: 716-898-8660
=09
http://www.chtsb.org <http://www.chtsb.org/>
=09
http://www.hwi.buffalo.edu <http://www.hwi.buffalo.edu>
<http://www.hwi.buffalo.edu/>
=09
=09
=09
=09

Sponsored Links