Hi,

I'm trying to design a database to keep track of information about
equipment. The trouble is, not all of the equipment is the same, so the
information I need to track about one type of equipment is not
necessarily the same as that for another type. I'm not quite sure how to
incorporate it all into one database, or even if that's the best option.

To make the problem a little more concrete, suppose I have a list of
widget-makers and another list of gadget-pushers. Each has a name, a
model number, a manufacturer, a price, etc. But widget-makers have
throughputs defined in terms of pieces/hr while gadget-pushers have
throughputs defined in terms of inches/sec (with no fixed mathematical
relationship between the two, so conversions aren't possible). Making
matters worse, widget-makers require oxygen and charcoal while
gadget-pushers instead run on chocolate and lattes.

This leaves me with three categories of data types:
(1) Common data (model number, manufacturer, etc.)
(2) Similar data (throughput)
(3) Dissimilar data (oxygen and charcoal requirements vs. chocolate and
latte requirements)

Handling the common data is easy, but I'm not quite sure how to deal
with the other two categories. I've considered having a field to
identify the type of equipment, then having separate tables for each
equipment type (e.g., if a piece of equipment is identified as a
widget-maker, it provides a foreign key to a table that contains only
widget-makers). This means that the software calling the database will
have to know what to expect for each equipment type.

This all seems quite reasonable to me, but I haven't been able to find
any examples of such a design. While I'm not a total newb to this stuff,
I have little theoretical background and I don't really know what you'd
call this problem, and perhaps that's why I haven't been able to find
examples. At any rate, I thought I'd run it past folks in the know to
make sure it's sane and to see if there are any better ideas that I
haven't run across yet.

Any suggestions? I've got "Database Design for Mere Mortals," but if
there's some resource that specifically address the type of problem I'm
facing I'd love to see it.

Thanks!

This can be modeled using supertypes and subtypes. There are a couple
of ways to model supertypes and subtypes in the relational model. Here
is a quick example:

Let equipment(model-number, manufacturer), widget-maker(model-number,
throughput), gadget-pusher(model-number, throughput) be three relation
schemes. Both widget-maker.model-number and gadget-pusher.model-number
reference equipment.model-number. equipment is the supertype and
widget-maker and gadget-pusher are subtypes of it.

There is a limit to what you can do with this though.

R.H. Allen wrote: