Questions tagged [single-table-inheritance]

Single table inheritance is the simplest of several ways to design SQL tables that reflect a class/subclass or generalization/specialization relationship.

Single table inheritance is the simplest of several ways to design SQL tables that reflect a class/subclass or generalization/specialization relationship.

Inheritance is a central concept in object modeling, and is dealt with in class/subclass models. It is well understood by people who build object oriented systems. The parallel concept in ER (Entity-Relationship) modeling is called generalization/specialization. Unfortunately, many introductions to ER modeling do not present generalization/specialization, leaving the beginner to reinvent the concept on their own.

SQL, as such, has no mechanism for implementing inheritance. There are several design techniques for mimicking the effects of inheritance in SQL tables. The simplest of these techniques is called single table inheritance.

In single table inheritance, a single table is used to retain data that pertains to either the superclass or any of its subclasses. Each attribute will have its own column, and each instance will have its own row.

The result of this design is that all data about any member of the class can be obtained without doing any joins. If the intersection of a given column and a given row is not applicable, it is left as an SQL NULL.

SQL NULLS do result in slower retrieval of the rows that contain them, but this is generally offset by not having to do joins. SQL NULLS do increase the amount of space needed to store rows that contain them, but this is generally a secondary consideration.

Where NULLS can be problematic is when they appear in Boolean comparisons like equality tests in WHERE clauses. In SQL a boolean test can result in three possibilities: TRUE, FALSE, or UNKNOWN. If either side or both sides in a comparison are NULL, the result is UNKNOWN. If a test for equality yields UNKNOWN, the same test for inequality will also yield UNKNOWN. This can be baffling to people who are used to two valued logic. The use of nullable columns in WHERE clauses has to be considered very carefully, in order to avoid unexpected results.

Another issue is that it can be difficult to tell which subclass a given row belongs to. For this reason, a separate column, often called EntityType (e.g. VehicleType), is used to indicate subclass membership explicitly.

In complex situations, there are two alternatives to single table inheritance. One is called class table inheritance, which has its own tag: . another is called concrete table inheritance.

616 questions
2
votes
0 answers

Active Admin and STI model error: Could not find table 'categories'

Update #1: Solved! Somehow Pow did not restart it's processes, so after updates it was loading the old config files. And it was loading the wrong locale file. I deleted gems I installed recently (only from Gemfile) and restarted Pow. Then one by one…
Ivan
  • 874
  • 10
  • 32
2
votes
2 answers

Rails: uninitialized constant PostsController::TextPost

In my App, I have different kinds of posts people can make. So I had the idea to incorporate the Single Table Inheritance for this: class Post < ActiveRecord::Base has_many :comments end class TextPostValidator < ActiveModel::Validator def…
Lanbo
  • 15,118
  • 16
  • 70
  • 147
2
votes
1 answer

How to filter inherited Doctrine objects?

Each Product is "owned" by a given Tenant (i.e. user) and requires a color which could be either a standard Color available to all tenants or a proprietary TenantOwnedColor which was created by a given tenant and only available to that…
user1032531
  • 24,767
  • 68
  • 217
  • 387
2
votes
0 answers

Unable to Accomplish Single Table Inheritance using Entity Schema Definitions in Typeorm

I have been attempting to achieve Single Table Inheritance with typeorm using Schema Definitions without success. My entities have a hierarchy with an abstract base class Attendee and would like to use an enum property named type as the…
2
votes
1 answer

Rails modeling - give user choice between two related models (sti) in the same form

Here are my models: class BillingProfile < ActiveRecord::Base belongs_to :student attr_accessible :cost end class PerEventBillingProfile < BillingProfile belongs_to :event_category end class FlatFeeBillingProfile < BillingProfile …
UrLicht
  • 939
  • 1
  • 14
  • 25
2
votes
1 answer

Hibernate single table inheritance - base class nested as a property inside a subclass throws PropertyAccessException

I have the following class structure with 2 base classes (Filter and Map). @Entity public abstract class Filter { } @Entity public class AFilter extends Filter { } @Entity public class BFilter extends Filter { } @Entity public abstract class Map…
nilgun
  • 10,460
  • 4
  • 46
  • 57
2
votes
1 answer

Single Table Inheritance with Formtastic

So I have two models that inherit from another as follows: class OneTime < Step has_one :due_date accepts_nested_attributes_for :due_date end class Repeatable < Step has_many :due_dates accepts_nested_attributes_for :due_dates end class…
2
votes
1 answer

Rails - Single Table Inheritance problems. Any solutions / alternatives

For my project management application, I am currently using Single Table Inheritance so that: Lead < Requirement Project < Requirement By which I mean to say that Lead is a Requirement and Project is a Requirement. It was okay, while I had these two…
2
votes
4 answers

Does the Class Diagram present the Database structure?

I am starting a whole project by myself for the first time, and I am stuck between the UML modelization ( Class diagram ) and the database structure. Should I use the exact same classes that I model in the class diagram in the database? For…
2
votes
1 answer

Single Table Inheritance to refer to a child class with its own fields

I am using Ruby on Rails 3 and I implemented a working Single Table Inheritance like the following: class User < ActiveRecord::Base # Schema Information # # Table name: User # # id : integer # type : string #…
user502052
  • 14,803
  • 30
  • 109
  • 188
2
votes
2 answers

JPA: Named Query on inherited JPA Entities

I have a entity University having a list of abstract Person. There are two implementations Student and Professor. Now I would like to find all Universities without any Student by a JPA Query. University-Class @Entity public class University { …
Rokko_11
  • 837
  • 2
  • 10
  • 24
2
votes
0 answers

Single Table Inheritance sharing the same model name: how to translate the model name of derived classes?

I have an abstract class WcagElement, and several derived classes that share the same model name so I can share the views, routes, etc. between all of them: class WcagElement < ApplicationRecord # See…
Joshua Muheim
  • 12,617
  • 9
  • 76
  • 152
2
votes
0 answers

Rails many-to-many association with single table inheritance

This problem involves a rails ActiveRecord many-to-many association and a case of single-table-inheritance. I am struggling to get the association Patient -> Membership to work. All other associations work as expected. Note that Member (below)…
2
votes
1 answer

Active Record, Polymorphic Has Many Through with STI

I'm having some trouble with a Polymorphic Has Many Through association with STI. Let me explain what I'm trying to do: Let's say I have a Contract. A Contract can have many Companies as parties to the agreement, namely, a Contract can have more…
2
votes
2 answers

Using devise with two types of similar users

I know this has been asked a bunch of times but I can't find a good answer. I'm going to have both merchants and users in my application. The table structure for both would be nearly identical so I would prefer to use one table and a boolean field…