I've a dilemma in my application and I don't get how to solve it at DB level. The idea is that a user can be subscribed to one or more services but services are show depending on user type. This can sound tricky but it does not. So I have a form where I pick which type of user I'm registering but then from services table I need to show only the services allowed for that type of user. I made this table:

CREATE TABLE "nomencla"."service" (
    "id" int4 NOT NULL,
    "name" varchar(80) COLLATE "default" NOT NULL,
    "active" bool,
    "cedulabenefsigesp" varchar(10) COLLATE "default" NOT NULL,
    "user_type" text COLLATE "default",
    CONSTRAINT "tipo_servicio_pkey" PRIMARY KEY ("id")

ALTER TABLE "nomenclator"."service" OWNER TO "postgres";
COMMENT ON COLUMN "nomenclator"."service"."user_type" IS '(DC2Type:array)';

In user_type column, I stored, as serialized values, the allowed user type for each service, ex:

1   Service1    t   0000000039  a:1:{i:0;s:1:"1";}
2   Service2    t   0000000040  a:3:{i:0;s:1:"2";i:2;s:1:"3";i:3;s:1:"4";}

For row 1 it means: Service1 will be available for users of type 1 and Service2 will be available for users of type 2,3 and 4 only so in register form for users of type 1 I should only show the Service1 option and for users of type 2,3 or 4 then Service2 will be showed, I need some help building a query for get that data or help to change my model with better solution to that problem, any?


It's not a good idea to encode access rules in text fields. Even more generally, it violates normal form to encode anything in any field.

Instead, create a new table:

    service_id int foreign key references service,
    user_type int);

If there's a row for the service's id and the user's type, then the user is allowed to order that service.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.