Choice- or ids-list- field representation

To some extent any solution is going to depend to a great deal on exactly what you are doing and what RDBMS you are using. In general though using a text field to store a comma separated list of integers is a bad idea.

The starting point solution is always id-based reference. This is a good place to start because it obeys proper relational rules, and it allows you to handle things like multiple values by breaking these off into new relations. In fact I would say it is always best to start here.

Alternatives however, work well in some cases and with some RDBMS's. ENUM fields (or alternatively text domains with a check to keep them within some values) are helpful if you never need to drop options. If you do, then you have to determine what you want to do about this before you drop them. Secondly array types can be useful in certain cases too (and they don't necessarily break 1NF if the array as a whole is an atomic value of a domain, for example if we represent an IPv4 address as an array of 4 8-bit integers). Array-based approaches depend highly on what you are doing and db-level support, but on PostgreSQL I would choose an array where cardinality matters (i.e. an array is a tuple instead of a set), and where the semantics are entirely self-contained.

You haven't given us much to work with here, what is the "other table" about? Let's assume it's a Custom Priority for now.

To be able to set a foreign key on multiple types, you need to use Table Inheritance. There will be one master table, and both types "inherit" from it, using the same primary key field.

There are a few kinds of table inheritance. Single Table Inheritance uses nulls, but is far simpler. Class Table Inheritance is normalized, but is more of a pain.

PRIORITY
id
type {generic, custom}


PRIORITY_GENERIC
id pk fk PRIORITY
name


PRIORITY_CUSTOM
id pk fk PRIORITY
name
created_at timestamp


USER
id pk
name
priority_id fk PRIORITY


insert into priority values
(1, 'Generic'),
(2, 'Generic'),
(3, 'Generic'),
(4, 'Custom');


insert into priority_generic values 
(1, 'Low'),
(2, 'Medium'),
(3, 'High');


insert into priority_custom values
(4, 'Crazy Super High', current_timestamp);


insert into user values (1, 'neil', 3);
insert into user values (2, 'chris', 4);