4 ms·
> Because if you put comma-delimited phone numbers into a field, you lose the ability to relate and join those phone numbers individually to another table. Whic
by zozbot234 5y ago
> Because if you put comma-delimited phone numbers into a field, you lose the ability to relate and join those phone numbers individually to another table. Which is bad
It's bad if you expect that ability in the first place! If the data you're storing (e.g. a list of phone numbers) will only ever be treated as a unified blob without any finer senantics, storing it as a single attribute is not a 1NF violation.
- mannykannot 5y agoAmong 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 agoWikipedia 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 agoLogical, 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 agoRelations, 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.
- crazygringo 5y agoTechnically yes, but this is where you have to use common sense. The assumption that something "will only ever be treated as a unified blob" can turn out to be a real footgun -- suddenly you need to locate all users who share a phone number and, whoops, you can't. Nobody's saying strings should be split into individual characters, or small JPEG thumbnail blobs ought to be decomposed into fields for each header. But if you're dealing with obvious common-sense pieces of data like phone numbers? That experience ought to tell you have a reasonable chance of needing to be joined relationally at some point? Then the smart thing to do is to interpret that as a 1NF violation.
- lisper 5y ago> Nobody's saying strings should be split into individual characters, or small JPEG thumbnail blobs ought to be decomposed into fields for each header. That could actually be useful if you want to be able to, for example, find all the jpegs with dimensions in a certain range.
- goto11 5y ago> That experience ought to tell you have a reasonable chance of needing to be joined relationally at some point? Then the smart thing to do is to interpret that as a 1NF violation. The smart thing is to separate phone numbers into separate fields if you need to treat them separately. But I don't see what this has to do with 1NF? Something can be bad design without being a 1NF violation and vice versa.