Re: Modeling Question

Modeling inheritance in a relational database generally follows one of three patterns.

1. Put the super-class attributes in one table. Put the sub-class attributes in there own tables. This is what you proposed. It is also common that the super-class table contains a "type" attribute to distinguish between sub-classes. The primary key of the sub-class tables would be the same as the super-class table. The primary key of the sub-class table would also be a foreign key to the super-class table.
2. Put all attributes in one super-class table with an additional "type" attribute. Each sub-class is a view of the super-class table that specifies its "type" in the WHERE clause.
3. Put all attributes of each sub-class in their own tables. The super-class is a view that unions the sub-class tables.
4. A mix of 1) and 2). Replicate frequently queried sub-class attributes in the super-class table.

Option 1 comments

Queries returning instances of a sub-class require a join. Lots of sub-classes means lots of tables.

Option 2 comments

No joins. All sub-class attributes must allow nulls or have a default. Few tables (one) but many attributes. If queries against non-key sub-class attributes are common, then indexing becomes problematic.

Option 3 comments

No joins. The super-class view requires maintenance when a new sub-class is added.

Option 4 comments

More complicated. Avoids joins in performance critical applications.

"John Bossert" <jbossert_at_usa.net> wrote in message news:3C898CCB.3030404_at_usa.net...
> I have two kinds of Parts: Telephones and Accessories. Only certain
 Accessories
> can be sold with certain Telephones.
>
> I'm thinking of creating a Parts table, with two sub-tables: Phone_Details
 and
> Accessory_Details to hold the type-specific information.
>
> Then, I'm thinking of creating a Phones_Accessories intersection table
 that
> would contain two Part_ID fields - one for the part:telephone and one for
 the
> part:accessory.
>
> Is there a better/alternative way to normalize this kind of arrangement?
 I
> really don't want to create separate tables for Telephones and
 Accessories. Thanks.
>
> --
> John Bossert
>
> In what concerns you much, do not think that you
> have companions: know that you are alone in the world.
>
> -- Henry David Thoreau
>
Received on Sat Mar 09 2002 - 16:44:21 CET