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")
);