Re: Need help in database design

public inbox for [email protected]  
help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Divyansh Gupta JNsThMAudy <[email protected]>
Cc: Ron Johnson <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Need help in database design
Date: Mon, 23 Dec 2024 14:03:12 -0700
Message-ID: <CAKFQuwabmqhgfJ5_8RfpzT2XhHWhGJ7rd2Tq7EAV0LfcuF=NVQ@mail.gmail.com> (raw)
In-Reply-To: <CAHesJ5KKgX0abQy5Yi=wdA_gvv3zXmk9rFKcKKR4awoz=B68zA@mail.gmail.com>
References: <CAHesJ5LES3aTDf=xp7NOwrADQ_HWC-Spsv7yLu9ZY+zxzZO53A@mail.gmail.com>
	<[email protected]>
	<CAHesJ5+ASNoSNMiC5Ms0Ts=gw7v2_UeBpUT=phujO4yE_XCbEw@mail.gmail.com>
	<CAHesJ5JbkCBZ2f_AvUr8+KWnGPAsobu4zyfnWm8bEeb7X9oqDQ@mail.gmail.com>
	<[email protected]>
	<CAHesJ5JLzhHiGSBSkJZ7x7rGgHeeByP=wWk1D5GG=x8cJ5YY6Q@mail.gmail.com>
	<CAKFQuwYdpzwcbSdQ8TvZ-nVjPeHVVz+5=bWofCbUK+p_o=axrQ@mail.gmail.com>
	<CAHesJ5+yTenkAxOT8H33Cfe=1b2kSyXGqxFYfYz5fgYAVVvFmw@mail.gmail.com>
	<CAHesJ5KaJ8p7QhB9UUoFEbA87cU7ke4GBMkKR3q2FJPVv9GXyw@mail.gmail.com>
	<CANzqJaB_s8eXCZJvYO9CLvgJNqrshD=G5GgECi1M9=vk-JHjdQ@mail.gmail.com>
	<CAHesJ5LgLi9-uGCk3J9TUkuyttysz3fzTaP+o57EjcBtwDYKZA@mail.gmail.com>
	<CANzqJaD-MwXzvg97q0iLvAdkf=DnUMOq0Ex2_eNU7sTxEL7bfA@mail.gmail.com>
	<CAHesJ5KtKm9fjhMdR1+cC-M5jW98Sz6sWKbt0mN6SJcfkq9eig@mail.gmail.com>
	<CAHesJ5Kne6MZakdhcQ9Zc-5KhBvhgUt+zUXuX5v6z+zwTY6gLQ@mail.gmail.com>
	<CAHesJ5KKgX0abQy5Yi=wdA_gvv3zXmk9rFKcKKR4awoz=B68zA@mail.gmail.com>

On Mon, Dec 23, 2024 at 11:26 AM Divyansh Gupta JNsThMAudy <
[email protected]> wrote:

>
> gdid int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1
> MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
>
> userid int8 NOT NULL,
>
> useremail varchar(600) NOT NULL,
>
> title public.citext NULL,
>
> authorname varchar(600) NULL,
>
> authoremail varchar(600) NULL,
>
> updated varchar(300) NOT NULL,
>
> entryid varchar(2000) NOT NULL,
>
> lastmodifiedby varchar(600) NULL,
>
> lastmodifiedbyemail varchar(600) NULL,
>
> "size" varchar(300) NULL,
>
> contenttype varchar(250) NULL,
>
> fileextension varchar(50) NULL,
>
> docfoldername public.citext NULL,
>
> folderresourceid public.citext NULL,
>
> filesize int8 DEFAULT 0 NOT NULL,
>
> retentionstatus int2 DEFAULT 0 NOT NULL,
>
> docfileref int8 NULL,
>
> usid int4 NULL,
>
> archivepath varchar(500) NULL,
>
> createddate timestamp(6) DEFAULT NULL::timestamp without time zone NULL,
>
> zipfilename varchar(100) NULL,
>
> oncreatedat timestamp(6) DEFAULT clock_timestamp() NOT NULL,
>
> onupdateat timestamp(6) DEFAULT clock_timestamp() NOT NULL,
>
> startsnapshot int4 DEFAULT 0 NOT NULL,
>
> currentsnapshot int4 DEFAULT 0 NOT NULL,
>
> dismiss int2 DEFAULT 0 NOT NULL,
>
> checksum varchar NULL,
>
> typeoffile int2 GENERATED ALWAYS AS (
>
> CASE
>
> WHEN authoremail::text = useremail::text THEN 0::smallint
>
> ELSE 1::smallint
>
> END) STORED NOT NULL,
>
> parquetfilename varchar(100) NULL,
>
> metadata_vector tsvector GENERATED ALWAYS AS (to_tsvector('english'::
> regconfig, (((((COALESCE(title::character varying, ''::text::character
> varying)::text || ' '::text) || (COALESCE(docfoldername::text, ''::text)
> || ' '::text)) || (COALESCE(authorname, ''::text::character varying)::text
> || ' '::text)) || (COALESCE(fileextension, ''::text::character varying)::
> text || ' '::text)) || (COALESCE(lastmodifiedby, ''::text::character
> varying)::text || ' '::text)) || COALESCE(contenttype, ''::character
> varying::text::character varying)::text)) STORED NULL,
>
> isfileencrypted int4 DEFAULT 0 NULL,
>
> addons_json jsonb DEFAULT '{}'::jsonb NULL,
>
> CONSTRAINT googledocs_tbl_clone_pkey PRIMARY KEY (gdid, userid),
>
> CONSTRAINT fk_googledocs_tbl_clone_users_tbl FOREIGN KEY (userid)
> REFERENCES dbo.users_tbl(uid) ON DELETE CASCADE
>
> )
>
> PARTITION BY HASH (userid);
>
>>
>>>>
There are a lot of unconventional choices for data types and column naming
there...and it doesn't seem as normalized as it could be.

Besides that, consider that this probably should be at least two tables.
Put columns that you expect to change whenever the user makes a typical
edit on one table.  Put those columns that can never change, or at least
would rarely do so, on another.  Narrower tables is usually a win and with
this division you aren't throwing away and replacing all of the static data
each time the volatile data changes.

David J.

view thread (19+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Need help in database design
  In-Reply-To: <CAKFQuwabmqhgfJ5_8RfpzT2XhHWhGJ7rd2Tq7EAV0LfcuF=NVQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox