PostgreSQL and table inheritance

database-designinheritancepostgresql

I'm not sure if this is the right place for this, so please feel free to tell me if it is off-topic or there is a better place to post.

I am working on a fantasy sports based project, and the number of tables is getting unruly. I have tables like nba_players, nfl_players, etc, and nba_players_to_teams, nfl_players_to_teams, etc. (NBA and NFL are basketball and american football leagues, respectively). These all share the same table schema and code-base, but have their foreign keys on different tables.

My question is, does using table inheritance make sense for situations like this? The "base" table would sit empty, and would only be used to enforce the schema. Will changes to the "base" table propagate to tables that inherit from it? At this point the schemas are pretty stable, so that's not a huge concern.

Any thoughts or concerns that I haven't addressed? I'm still reading about table inheritance and the pros and cons, but any resources you have would be greatly appreciated.

Best Answer

There is nothing in your question that would speak against inheritance.

One major limitation of the current Postgres implementation of inheritance is that FK, PK and UNIQUE constraints only apply to individual tables, not including children. But that seems to match what you need exactly: Create PK and FK constraints on each child table individually.

nba_players, nfl_players etc. could conveniently inherit from a general parent table players (possibly in a separate schema for empty parent tables). ALTER TABLE on the parent cascades to all children (except for ALTER TABLE ... RENAME). This makes it easy to run queries on all player tables together by targeting the parent.

If you never need to look at all player tables together or cascade changes to all tables, it might be better to just keep a template table (possibly in a separate schema for template tables) and create instances with CREATE TABLE ... (LIKE ...):

CREATE TABLE nba_players(LIKE players);

Related Solutions

Postgresql – Inheritance and its alternatives in PostgreSQL

It is hard to answer the question as written. The simplest solution would probably be something like a context table with types listed as they are. What I am not sure about is what you need in terms of linking to this table. In other words, have all the common fields for context in the context table plus a type field that tells you what kind of context it is. Other tables can then join that to provide more information about the types.

The problems you mention with the 1:1 design can be solved with composite foreign and primary keys. It may seem like field duplication but keep in mind your primary key is multi-field so your foreign keys would need to be also. This allows you to ensure that different dependent tables do not overlap where you do not want them to do so.

The second solution is to use inheritance. In my view, inheritance is going to be a good solution if the following are true:

The context records have foreign keys that reference other tables depending on subtype, and
No tables reference context as such but rather the subtype directly.

My guess is that in your case the second condition will not be met and therefore I would not recommend table inheritance for you.

Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

I solved this (for single inheritance) like so:

each extension of a class inherits, as its primary key the base classes primary key and always targets with its fk the immediate base class:

class A
class B extends A
class C extends B

A(id,p1)
B(id,p1,p2)
B(id,p1,p2,p3)

Table A:
id: primary key
p1: sth

Table B:
id: primary key + foreign key -> A.id
p2: sth

Table C:
id: primary key + foreign key -> B.id
p3: sth

this would reqiure a join to completely retrieve B or C.

alternatively you can have each extension mirror the properties of the base classes:

Table A:
id: primary key
p1: sth

Table B:
id: primary key + foreign key -> A.id
p1: sth
p2: sth

Table C:
id: primary key + foreign key -> B.id
p1: sth
p2: sth
p3: sth

here you can retrieve any class without joins but data is duplicated and has to be kept consistent.

which solution you can use strongly depends on how you want to retrieve the data and if you can live with the overhead of the join in version 1 or overhead of writing data multiple times in version 2.

I also added a meta-column to the table to know if a row does have extensions or not which is useful for a some queries:

Table A:
id: primary key
p1: sth
class: A,B,C

Table B:
id: primary key
p2: sth
class: B,C

Table C:
id: primary key
p2: sth
class: C

Best Answer

Related Solutions

Postgresql – Inheritance and its alternatives in PostgreSQL

Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

Related Question