Why store "collection" type in RDBMS considered violation of 1NF?

Question

Following this question

Wikipedia says:

http://en.wikipedia.org/wiki/First_normal_form#Repeating_groups_within_columns

A query such as "Which pairs of customers share a telephone number?" is more difficult to formulate

How could this be difficult to formulate? Intersect of two sets is pretty straight forward.

So why exactly don't RDBMS provide a way to store arraw/set/list ?

they do. A table is a set. Query results are a set. – Mitch Wheat Jun 17 '11 at 02:53 — Mitch Wheat, Jun 17 '11 at 02:53

score 2 · Accepted Answer · edited May 23 '17 at 11:48

In the article you linked to the table designs that were be compared to

Customer

Customer ID First Name Surname   Telephone Numbers
----------- ---------- -------   -----------------
123         Robert     Ingram    555-861-2025
456         Jane       Wright    555-403-1659, 555-776-4100
789         Maria      Fernandez 555-808-9633

vs.

Customer Name

Customer ID First Name Surname
----------- ---------- -------
123         Robert     Ingram
456         Jane       Wright
789         Maria      Fernandez


Customer Telephone Number

Customer ID Telephone Number
----------- ----------------
123         555-861-2025
456         555-403-1659
456         555-776-4100
789         555-808-9633

In the second design the query for "Which pairs of customers share a telephone number" could be (ignoring that the table and field names need to be quoted)

SELECT 
 a.telephone Number,
 a.Customer ID, 
 b.Customer ID
FROM 
 Customer Telephone Number a
 INNER JOIN  Customer Telephone Number b
 ON a.telephone Number = b.telephone Number

easy as pie

For the first design there isn't actually any Standard SQL for this. Each RDMS has its own way for parsing comma delimited fields and its usually a royal PITA and its not SARGable.

If you're interested in what it might take to parse a comma delimited field using try this SO search https://stackoverflow.com/search?q=sql+comma+parse

You'll probably find every RDMS under the sun in that search

Update From Comment

I didn't mean to write comma delimited string directly, but suppose if RDBMS can handle "collections" internally, SARGable operations could still exist

This is a somewhat different question. The answer is that some do. For example SQL Server's XML data type can do this and it is SARGable since you can create an index on them.

Does the XML data type violate NF1? If I recall correctly CJ Date argues no in "Database in Depth: Relational Theory for Practitioners", but I could see how some might.

but JOIN doesn't scale, I didn't mean to write comma delimited string directly, but suppose if RDBMS can handle "collections" internally, SARGable operations could still exist. MongoDB could even index nested documents. — est, Jun 17 '11 at 07:39
@est, for most purposes a join is much more powerful and convenient than functions to parse strings or handle arrays. The question is why would you *want* to put multiple values into an attribute position? "Multivalued" DBMSs do exist (e.g. Pick) but were long ago eclipsed by the relational/SQL model, largely because of the advantages of relational representation over other structures like arrays and lists. Arbitrarily stating that "JOIN doesn't scale" is just incorrect. Optimisation of joins is a different question and an altogether different discussion. — nvogel, Jun 17 '11 at 08:36
@dportas, OK, so why can't JOIN handle collections? Is it difficult to append a list to another? — est, Jun 17 '11 at 09:43
@est, a join is an operation on *relations* (aka "tables"). If the "collection" you are referring to is a relation then a join can handle it. If by "collection" you mean some data structure other than a relation then please define it. Tell us what you mean by a "join" on a collection data type and explain how it would be any different from a relational join. — nvogel, Jun 17 '11 at 10:03
@dportas, something like `ON a.telephone Number HAZ COMMON ITEM WITH b.telephone Number` ? — est, Jun 17 '11 at 17:43

score 1 · Answer 2 · answered Jun 17 '11 at 05:07

RDBMSs support relational algebra and languages based on relational algebra. The RA operates on relations, which are sets of tuples. So the best way to implement the intersection of two sets is by using relations rather than by using arrays or lists for example. Arrays and lists are quite different to relations (for example they are addressable by position) so to apply relational operations like intersection you would first have to convert the array or list to a relation.

http://en.wikipedia.org/wiki/First_normal_form#Repeating_groups_within_columns

The example given is one of using a comma-delimited string to represent a list of phone numbers. As the article points out, this doesn't violate 1NF - a string with commas in it is still a single value. However, it isn't a very practical design because it means you need a sequence of string manipulations to deconstruct the separate phone numbers in order to compare them to something else.

Keep in mind that SQL is not a truly relational language and a DBMS based on SQL is not strictly an RDBMS. In a real RDBMS it might be possible to use nested relations (relations within attributes) for sets of values within a single attribute. Some SQL DBMSs do support nested tables but they aren't widely used because the syntax to use them is more complex and they can easily be avoided by creating new non-nested tables.

that's exactly what I am wondering: why RDBMS don't implement "collections manipulation" internally, so operations like "compare" works? We don't actually need any string manipulation if RDBMS support collection operations out-of-box — est, Jun 17 '11 at 07:36

score 0 · Answer 3 · answered Jun 17 '11 at 12:59

Object-relational DBMS do. They allow to design classes (e.g. CLR types in MS SQL Server) and use them as generic types. So you can make list, array, dictionary or whatever in your class. But generally you don't because there are sets in RDBMS which are called tables. Incapsulating a set of values into one value is usually not good for performance, because you cannot use relational operations (provided by SQL) on it. 1NF is tricky, because it is not always clear which is considered as a single value or as a set of values. It mostly depends on how you process your data.

Why store "collection" type in RDBMS considered violation of 1NF?

3 Answers3