7

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

3
+50

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.

  • So to put in a nutshell, there is no common solution, it highly depends on one's needs. However the second solution I raised looks better and more scalable than the former. Do I well understood ? – Antoine Pinsard Feb 8 '13 at 17:55
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);
  • Sorry if I didn't make myself clear but I don't think the data stored in the other table matters. This could be users, products or bananas. What is important is that if I don't have a "generic" or pre-defined value in the field, the custom value is a LIST of rows from the other table. So basically a comma-separated list of IDs. I will rewrite my question, it might not be well-expressed. – Antoine Pinsard Feb 19 '13 at 20:30
  • ok. my schema should still work for you. you don't want to store a csv list in a column. – Neil McGuigan Feb 19 '13 at 21:54
  • Actually I can't see how table inheritance solves the issue. I edited my question. Maybe it will remove any misunderstanding? – Antoine Pinsard Feb 19 '13 at 22:17
  • so this user setting can point to any table in your database? – Neil McGuigan Feb 19 '13 at 22:51
  • No, it points to a specific known table. My question is quite general, it is not for a specific project. But if it's clearer for you, just assume it points to the User table (which makes sense if the user wants to grant access to his profile page to "Everybody", "Friends", "Nobody" or a specific set of users for instance). – Antoine Pinsard Feb 19 '13 at 23:00
-1

The "XOR-paired columns" solution

This solution consists in two columns that work together:

  • A nullable character varying "ENUM" column that can value "High", "Medium" or "Low".
  • A virtual column that would actually be represented by a third table that makes the relation 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 NULL, the M2M relation would be used.

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.