patternMinor
Choice- or ids-list- field representation
Viewed 0 times
fieldidschoicelistrepresentation
Problem
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).
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).
Solution
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.
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.
Context
StackExchange Database Administrators Q#34128, answer score: 3
Revisions (0)
No revisions yet.