How do we design schema for user settings table for postgresql?-CodePudding

How do we design schema for user settings/preferences table in a sql database like postgresql?

I am interested to know the proper way to design the schema of users_setting table where users are able to modify their settings. This seems to be a 1-to-1 relationship because each row of users table corresponds to a single row in the users_setting table

so this is like a 1-to-1 table relation between users and users_setting. Is this the wrong way to do this? I have searched online and could not really find any useful example schemas where users manage their settings. So here i am asking this question. I am certain this will help many people also

Here is what my current design looks like

DROP TABLE if exists users cascade;
DROP TABLE IF EXISTS "users";
DROP SEQUENCE IF EXISTS users_id_seq;
CREATE SEQUENCE users_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1;

CREATE TABLE "public"."users" (
    "id" bigint DEFAULT nextval('users_id_seq') NOT NULL,
    "email" text NOT NULL,
    "password" text NOT NULL,
    "full_name" text NOT NULL,
    "status" text NOT NULL,
    "is_verified" boolean NOT NULL,
    "role" text NOT NULL,
    "created_at" timestamptz NOT NULL,
    "updated_at" timestamptz NOT NULL,
    "verified_at" timestamptz NOT NULL,
    CONSTRAINT "users_email_key" UNIQUE ("email"),
    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

DROP TABLE if exists users_setting cascade;
DROP TABLE IF EXISTS "users_setting";
DROP SEQUENCE IF EXISTS users_setting_id_seq;
CREATE SEQUENCE users_setting_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1;

CREATE TABLE "public"."users_setting" (
    "id" bigint DEFAULT nextval('users_setting_id_seq') NOT NULL,
    "default_currency" text NOT NULL,
    "default_timezone" text NOT NULL,
    "default_notification_method" text NOT NULL,
    "default_source" text NOT NULL,
    "default_cooldown" integer NOT NULL,
    "updated_at" timestamptz NOT NULL,
    "user_id" bigint,
    CONSTRAINT "users_setting_pkey" PRIMARY KEY ("id")
) WITH (oids = false);


ALTER TABLE ONLY "public"."users_setting" ADD CONSTRAINT "users_setting_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "users"(id) NOT DEFERRABLE;

begin transaction;

INSERT INTO "users" ("id", "email", "password", "full_name", "status", "is_verified", "role", "created_at", "updated_at", "verified_at") VALUES
(1, '[email protected]', 'password', 'users1',   'active',   '1', 'superuser', '2022-07-05 01:05:50.22384 00',   '0001-01-01 00:00:00 00',   '2022-07-11 14:10:26.615722 00'),
(2, '[email protected]', 'password', 'users2', 'active', '0', 'user', '2022-07-05 01:05:50.22384 00',    '0001-01-01 00:00:00 00',   '2022-07-11 14:10:26.615722 00');

INSERT INTO "users_setting" ("id", "default_currency", "default_timezone", "default_notification_method", "default_source", "default_cooldown", "updated_at", "user_id") VALUES
(1, 'usd', 'utc', 'email',  'google',   300, '2022-07-13 01:05:50.22384 00', 2),
(2, 'usd', 'utc', 'sms',    'yahoo',    600, '2022-07-14 01:05:50.22384 00',    2);

commit;

so lets say i want to return a single row where a users.email is [email protected] for example, here is query i can run

select * from users, users_setting where users.id = users_setting.user_id AND users.email = '[email protected]';

id  email               password    full_name   status  is_verified role    created_at  updated_at  verified_at id  default_currency    default_timezone    default_notification_method default_source  default_cooldown    updated_at  user_id
1   [email protected]    password    users1  active  1   superuser   2022-07-05 01:05:50.22384 00    0001-01-01 00:00:00 00  2022-07-11 14:10:26.615722 00   1   usd utc email   google  300 2022-07-13 01:05:50.22384 00    1

i can have a single table for this but the table will get really long row-wise as i add more and more thing. user settings is just one, there are other tables similar to this. So will be great to know how to design a situation like this properly

CodePudding user response：

In your case a JSON could do the job:

ALTER TABLE public.users ADD user_settings jsonb NULL;

Update of settings will be something like:

UPDATE users
SET user_settings = '{"default_currency": "usd", "default_timezone" : "utc"}'
WHERE id = 1;

And select:

select * from users WHERE id  = 1;

You will find:

Also consider in Postgresql you can index a JSON, for example to query on a particular setting. Se here: https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING Specific:

Still, with appropriate use of expression indexes, the above query can use an index. If querying for particular items within the "tags" key is common, defining an index like this may be worthwhile:

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

CodePudding user response：

With this solution you can avoid JSON. Drawback is that setting_value cannot be tailored to exact type you need, compared to your first idea.
For example you can create:

CREATE TABLE public.user_setting (
    user_id bigint NOT NULL,
    setting_name text NOT NULL,
    setting_value text NULL,
    CONSTRAINT user_setting_pk PRIMARY KEY (user_id,setting_name)
);

ALTER TABLE public.user_setting ADD CONSTRAINT user_setting_fk FOREIGN KEY (user_id) REFERENCES public.users(id);

At this point I suggest you to have 2 query, one for users and one for settings:

SELECT *
FROM  user_setting us 
where user_id = 1;