4 ms·
Sorry but I think this author is being more pedantic rather than helpful, and actually seems to miss the point entirely. The crux here is two things the author
by crazygringo 5y ago
Sorry but I think this author is being more pedantic rather than helpful, and actually seems to miss the point entirely. The crux here is two things the author claims:
> Since SQL does not allow creating or using nested tables, most relational databases will be in first normal form by necessity.
> Many explanations (including Wikipedia) uses the example of a comma-separated string with phone numbers as an example of a 1NF violation... Make no mistake, encoding multiple values in a single string is generally a bad design. But it has nothing to do with first normal form. First normal form mean a column should not allow relations as values. A comma-separated string is still just a single string from the perspective of the database type system.
I think it's rather obvious that the example here is nothing to do with literal commas. But that storing multiple comma-delimited phone numbers in a single field is precisely creating a nested table in spirit.
It has nothing to do with SQL support for nested tables or the database type system -- it's not about formal definitions. It's about whether you're conceptually putting multiple pieces of data into a single field. Which has everything to do with first normal form.
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, and the whole concept of "1NF" exists to give a name to this badness.
And this isn't a minor point. It's actually extremely important, because the common practice of throwing JSON data into a string field destroys the ability to operate relationally on the data within it.
Now of course, plenty of databases these days actually do provide ways to index and join on data using a new "JSON" type column -- which is yet another form of the "nested table" the author claims doesn't exist. Which is great for combining ease of use with performance, but certainly isn't 1NF, and basically explodes (or at least dramatically complicates) the fundamental concept of relational databases as tables of rows and columns.
- 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.
- goto11 5y ago> it's not about formal definitions. It's about whether you're conceptually putting multiple pieces of data into a single field. Which has everything to do with first normal form. I'm really confused by this argument. It seems to you suggest "first normal form" does not just cover the formal definition (eliminate nested relations) but also a general guideline to avoid putting "multiple pieces of data into a single field". While the second part is certainly a very reasonable design guideline, it is a completely different thing than 1NF, and conflating these two things will IMHO just lead to a muddy understanding of both. The guideline to avoid conceptually putting multiple pieces of data into a single field cannot have a formal definition, since it depends on business requirements what constitute a piece of data. For example a phone number has multiple segments like country code, area code, line code etc depending on country. Should a phone number be treated as one piece of data or multiple pieces of data? Depends on what you need to do with the phone number. 2NF is defined to encompass 1NF, i.e. in order to be in 2NF a relation has to be an 1NF (indeed this is the case for all the higher normal forms). But if we confuse 1NF with some vague guideline without objective criteria, there is no way to determine if a relation is in 2NF either. This undermines the whole notion of normal forms.
- CRConrad 5y ago> The guideline to avoid conceptually putting multiple pieces of data into a single field cannot have a formal definition, since it depends on business requirements what constitute a piece of data. For example a phone number has multiple segments like country code, area code, line code etc depending on country. Should a phone number be treated as one piece of data or multiple pieces of data? Depends on what you need to do with the phone number. A phone number is what you dial to call someone; it's the value you need to use a telephone, which is what telephone numbers are for. A comma-delimited list of telephone numbers isn't. > According to Date's definition, a table is in first normal form if and only if it is "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions: > ... > 4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else). -- https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization , quoting Date, C. J. (2007). What First Normal Form Really Means. Date on Database: Writings 2000–2006. Apress. pp. 127–128. ISBN 978-1-4842-2029-0. Surely "exactly one value" from the applicable domain of telephone numbers is a telephone number, not a comma-delimited list of telephone numbers.