Choice- or ids-list- field representation

Question

I wonder what is the best way to represent a user setting that could either take a value from a set (such as 'High', 'Medium' or 'Low') or be a list of IDs from another (or the same!) table (Many to Many relation).

I would like to know what is commonly used and what are the drawbacks/assets of each solution (in terms of performances, integrity and sustainability).

Answer 1

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.

Answer 2

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);

Answer 3

Basically,I think about two solution:

The "raw text column"

This solution consists in text column which can value "High", "Medium", Low" or a comma-separated list of integers (e.g "1,73,192,203,27,17"). This solution doesn't take any benefit of the DBMS potential and leaves all the work to the application that will query the database. It seems to be the easiest solution to implement but requires to handle data integrity manually (if the row with ID 192 is deleted, 192 must be removed from the comma-separated list).

The "XOR-paired columns"

This solution consists in two columns that work together:

• A character varying "ENUM" column that can value "High", "Medium", "Low" or either be NULL.
• A virtual column that would actually be represented by a third table that refers the relations between the user table and the foreign table. Just as would do a classic Many-to-Many relation.

If the former is worth "High", "Medium" or "Low", the application would just take this value and the latter would just be ignored. On the other hand, if it's worth NULL, the M2M relation would be used.

Clearly, the "XOR-paired columns" solution looks much better than the "raw text column" solution. However I don't know if the latter could be more accurate in some case.

I also don't know if other solutions exist and in what case they could be useful.