Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

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.

share|improve this answer
    
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

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);
share|improve this answer
    
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
up vote -1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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