0

I want to understand inheritance in postgresql, simple whitch columns in whitch tables.

CREATE TABLE users (
  id serial PRIMARY KEY,
  username VARCHAR UNIQUE NOT NULL,
  email VARCHAR NOT NULL,
  password_salt VARCHAR,
  password_hash VARCHAR,
  avatar serial
)

CREATE TABLE groups (
  id serial PRIMARY KEY NOT NULL,
  name VARCHAR,
  email VARCHAR,
  avatar serial,
)

CREATE TABLE accounts (
  id serial PRIMARY KEY NOT NULL,
  name VARCHAR,
  avatar serial,
  rating json NOT NULL,
);

CREATE TABLE users_to_accounts (
  id serial PRIMARY KEY NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME,
)

CREATE TABLE account_subscryptions (
  user_id serial NOT NULL,
  account_id serial NOT NULL,
) INHERITS users_to_accounts

CREATE TABLE account_memberships (
  user_id serial NOT NULL,
  account_id serial NOT NULL,
) INHERITS users_to_accounts

CREATE TABLE users_to_groups (
  id serial PRIMARY KEY NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME,
)

CREATE TABLE group_subscryptions (
  user_id serial NOT NULL,
  group_id serial NOT NULL,
) INHERITS users_to_groups

CREATE TABLE group_memberships (
  user_id serial NOT NULL,
  group_id serial NOT NULL,
) INHERITS users_to_groups

Now.
1. Is it good design to have foreign keys in child tables an all common data in "abstract" table?
2. Is there any traps in future changes of database with inherited relantion tables?
3. I am all wrong and there is a better way for this schema?
4. I want to create good database schema and generate graphql api in postgraphile, looking in google for half day, did not gave me any one good or best solution, so every link will by great.

It may be usefull for others, I think. Thanks

Ankit Deshpande
  • 3,476
  • 1
  • 29
  • 42
Sakala
  • 91
  • 2
  • 5
  • What did your research show? See [ask] & the voting arrow mouseover texts. – philipxy May 14 '19 at 21:18
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy May 14 '19 at 21:19
  • @philipxy Mostly postgresql docs and similar stackoverflow answers. For me as frond end guy is complicated. I wish a simple example so i can build up my experience on it. – Sakala May 14 '19 at 21:33
  • Hi Sakala, the first step before (big work of) implementation is the (little work of) modeling... You can use [UML class modeling](https://en.wikipedia.org/wiki/Class_diagram) or simple [Euler diagram](https://en.wikipedia.org/wiki/Euler_diagram)... The important ideia is that each table is a set of elements. The set of `users_to_accounts` have subsets `account_subscryptions` and `account_memberships`: this is the central concept of inheritance. Other listed tables are cartezian products: the `account_subscryptions` = `users`×`accounts`... You must to model it as relationships, ER-diagram. – Peter Krauss May 15 '19 at 02:19

0 Answers0