0

Table "users" depends on "companies", "positions" and "employees":

create table companies(id serial primary key,
"name" varchar,
"comment" varchar,
lastmodified_datetime timestampz default now());

create table positions(id serial primary key,
"name" varchar,
"comment" varchar,
lastmodified_datetime timestampz default now());

create table employees(id serial primary key,
"name" varchar,
"comment" varchar,
firstname varchar,
middlename varchar,
lastname varchar,
isoffice boolean default true,
"position" integer references positions(id),
lastmodified_datetime timestampz default now());

create table users(id serial primary key,
"name" varchar,
"comment" varchar,
"login" varchar not null,
"password" varchar not null,
isadministrator boolean,
company integer references companies(id),
employee integer references employees(id),
lastlogged_datetime timestampz default now(),
lastmodified_datetime timestampz default now());

Each row has a "lastmodified_datetime" column to store time, but I would also like to store info about users (add lastmodified_user integer references users(id) columns).

This creates a situation when in order to create basic tables I need an already existing "users" table, but in order to create "users" tables, I need basic tables. I've also read in this SO question that circular references lead to a bad design.

How to implement it correctly in context of PostgreSQL? When I try to create a table that references another table that does not exist yet, PostgreSQL aborts the query which is logical. Should I create all tables first and then alter them to include these references or is there another approach?

CorellianAle
  • 645
  • 8
  • 16

1 Answers1

0

I don't think this is a problem, because the company and employee columns are null-able. So, you can insert a user who is then allowed to modify other users.

In a similar vein, you can have the last_modified_by_user (I think that name is more descriptive) and leave it NULL when records are created. The modification would then only be for change to the record, rather than for the original record.

You will have a problem if you attempt to make these columns NOT NULL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I guess I failed to ask the question itself. I've modified it: "When I try to create a table that references another table that does not exist yet, PostgreSQL aborts the query which is logical. Should I create all tables first and then alter them to include these references or is there another approach?" – CorellianAle Jul 07 '19 at 21:48
  • 1
    Yes you should. – user14063792468 Jul 07 '19 at 21:52
  • @CorellianAle . . . You need to use `alter table` to add some of the constraints for self-referencing tables. – Gordon Linoff Jul 07 '19 at 22:33