Re: DB Design Concepts

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

<http://www.hwi.buffalo.edu/>