If you have a choice of databases, choose PostgreSQL. Particularly if you are using something like MySQL only out of inertia.

-- Randal L. Schwartz, Perl hacker
Be sure to read my standard disclaimer if this is a reply.

1. 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.
2. 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.

1. 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.

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.

Here is a site that may help: Database Debunkings

Actually, it sounds like a case of what CJ Date calls the "First Great Blunder", which is equating object classes with tables (as opposed to domains/data types).

Date would probably suggest using three tables (common attributes in one, unique attributes in two others) and views to solve the problem that was originally posted. Unfortunately, no RDBMS today supports proper (always updateable) views, AFAIK.

What do you mean by "equate", and why is that a blunder? Our design for this object-oriented API is generally to allocate a separate table to each class that requires persistence. More specifically, if a class has a one-to-one relation with a particular attribute, that attribute gets a place in the table for the class. If there is a one-to-many relation then we have a separate table for the attributes, keyed to and constrained by a unique id in the class's primary (parent) table. If this is what you're suggesting is a blunder, I'm wondering what terrible fate should have befallen us in the previous four years of development :^)

As to the rest of your comment, I'm leaning toward the three table layout, although since the subclasses in my object model will require all of the contents in Asset and all of the contents in either DocumentAsset or MediaAsset, a join I think is more appropriate than a view. I think of views as more for taking a subset of data from several tables, but this is really a combination of everything keyed to a unique id.

Otherwise I'd keep them in one table, with a 'type' field that specifies the subclass name. When you fetch the row you can bless it into the appropriate subclass. Then you'll only have accessors to the relevant columns, as defined by the subclasses. All in MVHO.

-Nitrox

If you are using PostgreSQL then there's an explicit syntax to enable this sort of thing. PostgreSQL will then allow you to query on the assets table and see all the "objects" that are Assets. If you want to see just the DocumentAsset objects then use document_asset table. So far this schema by default won't create inconsistant Asset objects &em; the asset_id is controlled by a single sequence so if that's enough assurance you can proceed and know that asset_id is unique without explicitly constraining it.

 CREATE SEQUENCE asset_seq;
 CREATE TABLE assets (
     asset_id integer not null default nextval('asset_seq'),
     desk     text    not null,
     status   text    not null,
     priority text    not null,
     deadline date    not null
 );

 CREATE TABLE documentasset (
     doc_id integer not null unique,
     label  text    not null
 ) INHERITS (assets);

 CREATE TABLE mediaasset (
     media_id integer not null unique
 ) INHERITS (assets);
[download]

If you really have to ensure that asset_id is unique then you have to step outside of SQL. PostgreSQL has these nice inheritance features but doesn't yet allow you to constrain them declaratively. If you add on this PL/pgSQL code then it will constrain the inheritance for you. All of thsi is obviously just a start but it should get you thinking of the possibilities.

 /* From your command prompt type
    createlang plpgsql <database name> 
    
    It generates the following code automatically so it
    isn't nessessary to remember this next bit
  */
 CREATE FUNCTION "plpgsql_call_handler" ()
     RETURNS opaque AS '/usr/local/lib/plpgsql.so',
     'plpgsql_call_handler' LANGUAGE 'C';
 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
     HANDLER "plpgsql_call_handler"
     LANCOMPILER 'PL/pgSQL';
[download]

And now to create a PL/pgSQL function and hook it into the right place. Please be aware I just wrote this into a window and didn't try to run it first or even verify that it's syntactically correct. You'll want to read up on PostgreSQL's triggers and look into either implementing this sort of function in PL/pgSQL or maybe it's TCL or Perl implementations.

 CREATE FUNCTION asset_id_ins_upd () RETURNS OPAQUE AS '
 DECLARE
     found INTEGER;
 BEGIN
     IF TG_OP = ''UPDATE'' THEN
         -- Return OK for unchanging asset_id on
         -- update operations
         IF NEW.asset_id = OLD.asset_id THEN
             RETURN NEW;
         END IF;
     END IF;

     SELECT 1 INTO found
         FROM  assets
         WHERE assets.asset_id = NEW.asset_id;
     IF FOUND THEN
         RAISE EXCEPTION 
     END IF;
 END;
 ' LANGUAGE 'plpgsql' WITH (isstrict);
 
 CREATE TRIGGER assets_asset_id_ins_upd BEFORE INSERT OR UPDATE ON ass
+ets
     FOR EACH ROW EXECUTE PROCEDURE asset_id_ins_upd();
[download]

---

Fun Fun Fun in the Fluffy Chair

INHERITS, yes! But unfortunately, I'm not using Postgres. I've got Oracle 8i, and after searching their docs for "inherit", "derive", "subclass", "under" and "child" I can't find an equivalent keyword. All in all that's not a huge loss though, as we've tried to stay DB agnostic in our SQL to as great a degree as possible.

What are your thoughts on the principle of the matter as far as using joins here though? pg points out below that denormalization for performance is an accepted practice, but this application isn't really going to be hit that hard. Rather I'm concerned more with ease of maintenance and extensibility, working on the assumption that the child tables will expand down the road. Which, upon reflection, favors joins strongly, views a little less so IMO. Hmmm. /me dreams of INHERITS...

In general, I think object oreinted databases are more about buzzword compliance than really useful. However, I have found problems that seem to lend themselves more naturally to an OO data structure than a traditional relational structure. This seems to be one such case.