Re: Data (Table) Structure Question

> Richard, the latter method makes much more sense in my
> mind. i don't think scaling will be an issue (i'd
> *love* to have *that* problem!), but i want something
> that makes sense to me.
>
> however, i don't understand what an extenstion would
> do. wouldn't using normal tables with apporpriate
> primary / foreign key links work just fine?

If you are referring to a generalization hierarchy, you are using tables with primary keys and
foreign keys... However, each table has a one to one relationship. Modeling a parts catalog is a
problem lends itself well to a generalizatoin hierarchy.

At the (highest level)/(most general) table of the hierarchy would be a simple parts table all of
the information in this table is common for all parts in a catalog:

tbl_Parts:
PartNo | part_type | description | unit_cost |
--------------------------------------------------------
1 | motor | .25 hp DC motor | 50.00 |
2 | paint | silver spray can| 2.00 |
3 | motor | piston pump mtr | 40.00 |
4 | paint | gray 1gal latex | 15.00 |
5 | relay | 4PDT 24vdc relay| 20.00 |

This is a simple table but it doesn't allow you hold specific data about each type of part. In
this case you have three options. you can increase the size of you description field to around a
thousand characters so you have room to add a detail description, but this can be inconvient and
ugly to deal with.

or you can nullable column to this table that you filled in when it applies to a specific part.
This works well as long as you don't have many types of parts that require a specific field and
you only need a few nullable column to hold the additional details.

In the case of a parts table, I would like to have alot of additional columns to specify
additional detail about each part, and I could have hundreds of diffent part types. So this in
case, I would like to put the additional information the (lower level child/more specific)
auxillary tables.

Since I am only showing 3 part types I will only create 3 additional tables:
Motor, Paint, relay:

Motors:
PartNo|part_type|voltage | HP | SF |
--------------------------------------
1 |motor | 48VDC |.25 | 1.5 |
3 |motor | 120VAC | 5 | 1.25 |

if you joined tbl_parts to Motor you like:

select partNo, part_type, description, Price, voltage, hp, SF
from tbl_parts as P join Motors as M
on ( P.partNo, P.part_type) = ( M.partNo, M.part_type)
;

PartNo|part_type|description |unitc|voltage | HP | SF |
----------------------------------------------------------
1 |motor |.25 hp DC motor|50.00| 48VDC |.25 | 1.5|
3 |motor |piston pump mtr|40.00| 120VAC | 5 |1.25|

You use the same technique to create specific detail table that relate back to the main parts
table. I would do this for the paint table and relay tables.

At this point ( having created the motor,paint,relay table) I can start building relationships
between these child tables.

Lets say you wanted to track which relays that were sold with a certail motor, you would create a
relate table between the two.

motors --------->[ rlt_motor_relays]<----------- relays.

To help explain it further you can read about the postgresql extenstion that supports this:
http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html

Regards,

Richard Broersma Jr.