SQL Database Design » 7. Standard Data Types » 7.7 Collective Types

7.7 Collective Types

Collective types are a common term for all sorts of sets, arrays, lists, dictionaries, etc. The SQL standard only defines the keywords MULTISET and ARRAY. The SQL standard apparently is a little short on predefined collective types, offering only two keywords. All other types, like SET (MySQL) or VARRAY (Oracle), are DBMS-specific. Collective types generally define many-to-many relationships.

While digging through enums and sets in MySQL, I did find uses for ENUM, but found the SET type to be utterly strange. There is nothing more contradictory than a set type in the relational model, given that it is inherently about (entity) sets. So, why should anyone care? There is always a more natural, relational way of achieving the same or even better result. The problem with collective types is, that SQL has no operators that work on multi-values. For example, there are no operators to index into an array. Tables allow a lot more flexibility and operations to be performed on them than collective types.

In relational databases, another problem with collective types is that their use generally breaks the first normal form. Try to avoid them. Collective types indicate distinct entity types and cause troubles of their own. You can read more about that in the section 14. Normalization. Please also read section 8. User-Defined Types for more information on user-defined types.

Last updated: 2010-08-04