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.html
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
