Re: SQL Joins across Subclasses

There are different ways to look at this.

From a database design view:

If there is no strong reason, I would say always normalize your tables. Otherwise, you may not see a problem now, but big problems in the future. Believe me. The maintainance guys will blame you for the bad design.
However if what you are working on is some sort of data warehouse thing, and those tables have millions of rows. Don't hesitate to denormalize your database. In this case, nobody can blame you, you are not a black sheep. Denormalization is one of the conventional ways used in data warehouse to improve performance. This is on paper.

From a OO-design view.

You better have three classes defined. This is not related to the background database design, even if you decide to denormalize your tables.

For example you denormalize those three tables in your post, and only have one asset table, you still should have three classes: asset, media, and document asset. There is no reason to break the OO rules.

Also you will use lots more memory space, if you combine three objects into one, when you design your classes. There would be space occupied by those NULLs, BLANKs, ZEROes etc.

As a summary, don't denormalize your tables, unless you have a very good reason. If you have a very good reason, denormalization is definitely not a crime, and there are design methodologies to support you.

For your classes, always follow the rules.

Just want to add one more thing:

For database performance, join is usually not a big performance issue, although it will take some extra time. Because most of the time, join is an indexed access, not a full table scan, as long as your database is reasonably well designed, and your sql statement is carefully tuned.

Comment on Re: SQL Joins across Subclasses

Replies are listed 'Best First'.
Re: Re: SQL Joins across Subclasses by djantzen (Priest) on Jan 12, 2003 at 03:41 UTC
Blazing performance in this case is not the primary objective, rather it is maintainability and extensibility. So given that, I'm inclined to take your advice to normalize and use joins. As far as the object model goes, you're preaching to choir my friend :^) Even if I were to combine the classes into a single Asset, the differences would be minor enough to incur almost no performance degradation over the model with inheritance. However, as a matter of good OO design the latter clearly wins out. Update: In my object model I've decided to solve this dilemma by separating out the database interactivity according to which attributes belong to each class. In other words, `Asset` is responsible for saving only its attributes in its own table, and `DocumentAsset`'s database methods rely on SUPER to inherit the necessary behavior. Thus, `DocumentAsset::save` calls `$this->SUPER::save()` , and then proceeds to save only its distinct attributes in its own table, namely, `docid` and `label`. So far so good :^) thanks pg.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Re: SQL Joins across Subclasses
by djantzen (Priest) on Jan 12, 2003 at 03:41 UTC

Blazing performance in this case is not the primary objective, rather it is maintainability and extensibility. So given that, I'm inclined to take your advice to normalize and use joins.

As far as the object model goes, you're preaching to choir my friend :^) Even if I were to combine the classes into a single Asset, the differences would be minor enough to incur almost no performance degradation over the model with inheritance. However, as a matter of good OO design the latter clearly wins out.

Update: In my object model I've decided to solve this dilemma by separating out the database interactivity according to which attributes belong to each class. In other words, Asset is responsible for saving only its attributes in its own table, and DocumentAsset's database methods rely on SUPER to inherit the necessary behavior. Thus, DocumentAsset::save calls $this->SUPER::save() , and then proceeds to save only its distinct attributes in its own table, namely, docid and label. So far so good :^)

thanks pg.

[reply]
[d/l]
[select]