PostgreSQL and table inheritance

Question

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.

Answer 1

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 separate instances with CREATE TABLE ... (LIKE ...):

CREATE TABLE nba_players(LIKE players);