4 ms·

Among the principal tenets of Codd's database model (which is not intended to be a generalized model of relations) are that the database scheme should not intro

Among the principal tenets of Codd's database model (which is not intended to be a generalized model of relations) are that the database scheme should not introduce either accidental relationships or accidental constraints. If we represent a one-to-many relationship by making a CSV of the elements of the "many" side, then, for a given X standing in this relationship to A, B and C: 1) We have introduced an ordering that may not be relevant - is "A,B,C" the same as "B,A,C"? 2) We cannot find out whether X has this relationship to B without parsing the CSV, or which entities B stands in this relationship to. 3) We cannot add or remove instances of this relationship to X without updating the whole relationship. ...and so on. All these are accidental features of the data representation that are not inherent to the semantic domain. So, when you write "It's bad if you expect that ability in the first place!", you are essentially saying "if we put aside the principles of Codd's model and accept the very problems it was expressly intended to mitigate..." You can certainly use a relational database to store information in ways that preserve the sorts of problems they were intended to solve - and there will be cases where it does not matter. The normal forms are guidelines for using relational databases as intended, to minimize these problems, when it might matter.

goto11 5y ago
> The normal forms are guidelines for using relational databases as intended, to minimize these problems, when it might matter. I think the discussion comes down to different ideas about what "normal form" means. Is it some semi-subjective guideline about good design (like the SOLID principles in object-oriented design) or is it a set of unambiguously defined rules? Nobody is saying it is a good idea to dump a table as a CSV file into a single database field. That is not really the question. The question is if it constitutes a violation of first normal form. The article claims that it doesn't. The takeaway should not be to redefine first normal form into some vague notion of "the spirit of the relational model". The takeaway should be that normalization is not sufficient to create a good database design. Just as an example, having two tables containing the same information is obviously a bad design prone to a number of problems. But it doesn't actually violate any normal forms.
- CRConrad 5y ago
  > Nobody is saying it is a good idea to dump a table as a CSV file into a single database field. That is not really the question. The question is if it constitutes a violation of first normal form. The article claims that it doesn't. Seems to me that the article is quite simply wrong on that: > To satisfy First normal form, each column of a table must have a single value. -- https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization [@ "Satisfying 1NF"] A set of telephone numbers isn't "a value"; a telephone number is. (More on that further up the WP page.)
  - goto11 5y ago
    Wikipedia is imprecise in this passage. First normal form is defined in terms of domains, not values. Domains is basically the data type of a column. So the question is what the type of the column is. If the column type is string (or VARCHAR as in SQL) then it is not in violation of 1NF, since a string/varchar is not a relation, it is atomic with regards to the relational operators. Packing multiple conceptual values into a single string is probably not a great idea, but the issue is unrelated to the 1NF. If the database allowed nested tables, then a phone number field could contain a table where each row were an individual phone number. But this would be in violation of 1NF.
    - CRConrad 5y ago
      Logical, not physical, someone said: https://news.ycombinator.com/item?id=27569106 https://news.ycombinator.com/item?id=27569106 String is physical implementation. Logically, the domain is telephone number.
      - goto11 5y ago
        Relations, attributes, and domains all exist on the logical level. The relational model deliberately does not say anything about the physical level. Domains are the data types of columns. If a column has the type VARCHAR, then the domain is varchars. If a column has the type "phone number", then the domain is phone numbers. They exist on the same level. If the database does not support user defined domains, then you can be forced to store phone numbers as varchars rather than use a phone number type. But then the domain is varchar, not phone number.

goto11 5y ago

> The normal forms are guidelines for using relational databases as intended, to minimize these problems, when it might matter. I think the discussion comes down to different ideas about what "normal form" means. Is it some semi-subjective guideline about good design (like the SOLID principles in object-oriented design) or is it a set of unambiguously defined rules? Nobody is saying it is a good idea to dump a table as a CSV file into a single database field. That is not really the question. The question is if it constitutes a violation of first normal form. The article claims that it doesn't. The takeaway should not be to redefine first normal form into some vague notion of "the spirit of the relational model". The takeaway should be that normalization is not sufficient to create a good database design. Just as an example, having two tables containing the same information is obviously a bad design prone to a number of problems. But it doesn't actually violate any normal forms.

CRConrad 5y ago
> Nobody is saying it is a good idea to dump a table as a CSV file into a single database field. That is not really the question. The question is if it constitutes a violation of first normal form. The article claims that it doesn't. Seems to me that the article is quite simply wrong on that: > To satisfy First normal form, each column of a table must have a single value. -- https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization [@ "Satisfying 1NF"] A set of telephone numbers isn't "a value"; a telephone number is. (More on that further up the WP page.)
- goto11 5y ago
  Wikipedia is imprecise in this passage. First normal form is defined in terms of domains, not values. Domains is basically the data type of a column. So the question is what the type of the column is. If the column type is string (or VARCHAR as in SQL) then it is not in violation of 1NF, since a string/varchar is not a relation, it is atomic with regards to the relational operators. Packing multiple conceptual values into a single string is probably not a great idea, but the issue is unrelated to the 1NF. If the database allowed nested tables, then a phone number field could contain a table where each row were an individual phone number. But this would be in violation of 1NF.
  - CRConrad 5y ago
    Logical, not physical, someone said: https://news.ycombinator.com/item?id=27569106 https://news.ycombinator.com/item?id=27569106 String is physical implementation. Logically, the domain is telephone number.
    - goto11 5y ago
      Relations, attributes, and domains all exist on the logical level. The relational model deliberately does not say anything about the physical level. Domains are the data types of columns. If a column has the type VARCHAR, then the domain is varchars. If a column has the type "phone number", then the domain is phone numbers. They exist on the same level. If the database does not support user defined domains, then you can be forced to store phone numbers as varchars rather than use a phone number type. But then the domain is varchar, not phone number.