I am not sure when is correct to use supertypes for entities. I have a database in which different entities connect to each other (in the real world) via pipelines and it is these connections which cause me to think I should be using a supertype for all these connecting objects.

For example, at the moment there could be a table for houses, water treatment plants, water pumps. All of these connect via Water pipelines, which also is a table. From what I can see there are two options of how to store these connections:

1. That all these pipeline-connectable objects can exist in a supertype table so they each have unique ids compared to each other, and the table is called WaterInfrastructure say. Then a one junction table can hold the ids of connection objects and the pipeline that connects them.

2. Each type of object that can join to a pipeline can have it's own WaterPipelinesObjects (where object would actually be the name of the object) to store all these joins.

The supertype seems like the preferable method, although besides each entity being pipeline connectable, there are not very many attributes in common. I'm worried that perhaps a supertype table called "WaterInfrastructure" is too vague to have as a table.

Any comments, suggestions? Thanks for the help!

the supertype is certainly more convenient in terms of administering the foreign keys and reducing proliferation of tables

what might help you decide which way to go is to analyze a few typical queries

what sorts of information are your queries typically going to return?

for example, list the object names and connection date for all objects connected to the elm street pump

how would you do that query with the supertype, and how would you do it with separate object tables?