DB Design Concepts

------_=_NextPart_001_01C78CF0.C0E7638A
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

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?

=20

Do I go to 2nd NF simply because a column is not going to be populated
as often?

=20

Max H. Thayer

Lead Software Developer

Center for High-Throughput Structural Biology

=20

Hauptman-Woodward Medical Research Inst.

700 Ellicott St.

Buffalo, NY 14203

Phone: 716-898-8637

Fax: 716-898-8660

http://www.chtsb.org =20

http://www.hwi.buffalo.edu =20

=20


------_=_NextPart_001_01C78CF0.C0E7638A--

Re: DB Design Concepts

------=_Part_16945_14698256.1178135400925
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 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.hwi.buffalo.edu
>
>
>
>

------=_Part_16945_14698256.1178135400925--

RE: DB Design Concepts

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 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
=09
http://www.hwi.buffalo.edu

=09
=09
=09
=09

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: DB Design Concepts

------=_Part_18627_9370348.1178139009840
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Okay, so couldn't you just set a default value for the column (N for NULL).
This way column 1 and column 2 both contain valid data for whichever state
your column takes on.

Then just tell your logic to omit the results of column 2 that have a value
of "N". This way only your valid rows would be pulled and your value for "N"
would serve the purpose of a NULL as your logic changes the state.


On 5/2/07, Max Thayer wrote:
>
> 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]
> 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,
>
> 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 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.hwi.buffalo.edu
>
>
>
>
>
>
>
>

------=_Part_18627_9370348.1178139009840--

Re: DB Design Concepts

Max Thayer 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?

What kind of queries are you going to be running? Where you need both
columns all the time?

I'd suggest leaving them in the same table for a few reasons:

- If you're always joining the two tables there's no point in having
them separate.

- If you always need the NULL entries, you're going to have to LEFT
OUTER JOIN the two tables every time because table '1' will have an
entry but table '2' might not.

- You're going to gain performance with large datasets because the
database (mysql or any other type) doesn't have to join two tables and
match up entries and so on.


If on the other hand you are going to have a script that runs once a
month that queries both tables, this is all moot.


Is there another way you can do what you want? eg a stored procedure?
http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.htm l

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: DB Design Concepts

------=_NextPart_000_0007_01C78D5F.D097D750
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

actualy i'm not soo smart..
> Max Thayer 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?
can we see your table??
u can use my way for your problem. if you have access.. the lowest is fine.
I create the table from there and then i use relationship.

> What kind of queries are you going to be running? Where you need both
> columns all the time?
i ask same things.. hope my zip can help u


------=_NextPart_000_0007_01C78D5F.D097D750
Content-Type: text/plain; charset=us-ascii

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
------=_NextPart_000_0007_01C78D5F.D097D750--