Need some help with this query or some ideas for change it to better model

Question

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")
) WITH (OIDS=FALSE);

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?

Answer 1

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:

ServiceAllowedUserTypes(
    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.