2

using references:

CREATE TABLE Users (
    id       int     primary key,
    name     text
);

CREATE TABLE Moderators (
    role     int,
    userid   int references Users(id)
);

using INHERITS:

CREATE TABLE Users (
    id       int     primary key,
    name     text
);

CREATE TABLE Moderators (
    role     int
) INHERITS (Users);

Besides the differences in query syntax, is there any difference in performance, scalability of those two?

Hoang Hiep
  • 2,242
  • 5
  • 13
  • 17
  • 1
    Yes there are differences but it depends on the type of query which ones works best. Using references the disadvantage is that moderator data is split over two tables. But the advantage is all basic user information is in a single table. With inheritance all data for moderators is in a single table and the Users table only contains normal users. Personally I would use a design with a single class of users and give these users privliges. – Eelke Sep 23 '16 at 05:56
  • [This](https://stackoverflow.com/questions/3074535/when-to-use-inherited-tables-in-postgresql) could help too. – Rafs Apr 12 '23 at 11:41

1 Answers1

2

The inheritance in PostgreSQL is pretty old artefact. In these days the PostgreSQL is relation database, it is not OOP database.

If your model will be OOP only, then probably you will not touch some unwanted artefacts - but when you will try mix both models, then you can touch some issues - inheritance is not well supported by reference integrity constraints and maybe you can find some other gotchas. The OOP way is not used, is not preferred - it is just historic artefact.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • I have to say using inheritance to simplify database design (where you need certain columns everywhere) and to boost performance are two good reasons. [Postgres is an ORDBMS](https://www.postgresql.org/about/) really. – Rafs Apr 12 '23 at 11:39