Home > Archive > PHP DB > May 2007 > Re: [PHP-DB] DB Design Concepts
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: [PHP-DB] 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...procedures.html
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| actualy i'm not soo smart..
> Max Thayer wrote:
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.
[color=darkred]
> 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
| |
|
|
|
|
|