Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008
    Posts
    33

    Subtypes and Supertypes

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •