I have User and Category models with MANY to MANY relationships. Any User can have multiple categories, but one of them have to be main. Where is the right place for pointing to that?
I have User and Category models with MANY to MANY relationships. Any User can have multiple categories, but one of them have to be main. Where is the right place for pointing to that?
In pivot table.
Thanks! Am I right that pivot table is table, which is used to store N-M relation? And to clarify, am I supposed to add kind of is_default field to that table?
Like that:
[sql]
CREATE TABLE IF NOT EXISTS user_category_nm (
user_id bigint,
category_id bigint,
is_default boolean default FALSE
);
ALTER TABLE ONLY user_category_nm
ADD CONSTRAINT user_category_nm_pkey PRIMARY KEY (user_id, category_id);
ALTER TABLE ONLY user_category_nm
ADD CONSTRAINT user_category_nm_user_fkey FOREIGN KEY (user_id) REFERENCES "user";
ALTER TABLE ONLY user_category_nm
ADD CONSTRAINT user_category_nm_comment_fkey FOREIGN KEY (category_id) REFERENCES category;
[/sql]
Yes, that one.
Don’t forget to add some logic (triggers or hooks) to keep your data consistent.
You can also try adding a field like main_category_id to users table instead, but it can cause a lot of inconveniences.