Postgresql – Database design. And get last modified time with each column in PostgreSQL

auditdatabase-designpostgresql

My database/table each endpoint/scope have multiple table,
e.g user: user, user_information, user_role …
I was wondering should I separate table like below, or not?

This is my first time to build a product not like before just make some onetime work small website. and this it might add more feature in future version.

I'm not sure about is this overdesign? Does separate table like this any benefit in the future ??
and cons I know is use more table join, and more difficult to me to maintain build the query.

any suggestion, share experience will be really appreciate.

For now I only can imagine I might want to know each column last modified time?

So if I want to know each column last modified time, is there any original build method in PostgreSQL? or I have to add for each column like email_last_modified_date, username_last_modified_date …

endpoint/scope user 

CREATE TABLE IF NOT EXISTS "user"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "status" integer NOT NULL,
    PRIMARY KEY ("id")
  );

CREATE TABLE IF NOT EXISTS "user_information"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "user_id" integer NOT NULL,
    "email" varchar(100) NOT NULL,
    "username" varchar(50),
    "password" varchar NOT NULL,
    "first_name" varchar(50),
    "last_name" varchar(50),
    "website" varchar,
    "description" varchar,
    "birth_date" timestamp without time zone,
    "country" varchar(50),
    "gender" integer,
    "file_type" integer,
    "file_name" varchar(50),
    "file_extension" varchar(50),
    "file_portrait" boolean,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("user_id") REFERENCES "user" ("id") ON DELETE CASCADE ON UPDATE CASCADE
  );
CREATE TABLE IF NOT EXISTS "user_role"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "user_id" integer NOT NULL,
    "role" integer NOT NULL,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("user_id") REFERENCES "user" ("id") ON DELETE CASCADE ON UPDATE CASCADE
  );

merge table ?

CREATE TABLE IF NOT EXISTS "user"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "status" integer NOT NULL,

    "information_last_modified_date" timestamp without time zone,
    "information_last_modified_by_user_id" integer,
     .... user_information 

    "role_last_modified_date" timestamp without time zone,
    "role_last_modified_by_user_id" integer,
    ... user_role
    PRIMARY KEY ("id")
  );

Best Answer

I was wondering should I separate table like below, or not?

Well, one immediately obvious downside of the table design you posted is that there are no constraints on user_information or user_role ensuring that you don't have more than one such row in each of these tables for each user. And even if you remember to add that, you will still have the problem that it will be difficult or impossible to enforce the rule "every user must have a role (or other critical attribute)".

Where it typically makes sense to break out metadata like this is when you have either many-to-one relationship of this metadata (e.g. a single user may have multiple roles), or you have a lot (i.e. many columns) of optional metadata that is unwieldy to cram into a single table.

For now I only can imagine I might want to know each column last modified time?

So if I want to know each column last modified time, is there any original build method in PostgreSQL? or I have to add for each column like email_last_modified_date, username_last_modified_date ...

You'll have to create individual columns (e.g. "email_last_modified_date") and have a trigger function enforce that the column is set correctly to achieve this. A more flexible and useful approach is usually constructing an audit table, with a trigger on the "user" table saving the changed columns for every UPDATE, or the contents of the whole row for every DELETE. This is where json, jsonb, or hstore types come in handy, if you'd like a single audit table capable of dealing with several tables, or capable of dealing with many columns from a single user table without needing to know the structure of the user table.

Related Question