Re: Need help in database design

On Mon, Dec 23, 2024 at 11:26 AM Divyansh Gupta JNsThMAudy <
ag1567827(at)gmail(dot)com> 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.