Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What does First Normal Form mean?

cargocultcode.com

Post reply on HN
Print view View original

121–125 of 125 posts

Re: What does First Normal Form mean?

#121

Post by CRConrad »

goto11 wrote: post #118
CRConrad wrote: post #112

Earlier quoted context omitted.

> 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…

> Surely "exactly one value" from the applicable domain of telephone numbers is a telephone number, not a comma-delimited list of telephone numbers. Yes certainly. But note that when we talk about comma-separated values, we are talking about multiple values encoded into a string and treated as a single string (or VARCHAR) value on the database level. In other words, the domain is the set of strings, not the set of ph…

And an RDBMS on-disk structure is just a binary blob, so let's have those as values within another RDBMS... No, that's still a whole fricking database. Just because it's encoded into a single binary blob doesn't change that. Neither does encoding a list of telephone numbers into a string change the fact that it's a list.

Re: What does First Normal Form mean?

#122

Post by CRConrad »

goto11 wrote: post #116
CRConrad wrote: post #111

Earlier quoted context omitted.

> 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_nor…

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 valu…

Logical, not physical, someone said: https://news.ycombinator.com/item?id=27569106

String is physical implementation. Logically, the domain is telephone number.

Re: What does First Normal Form mean?

#123

Post by goto11 »

CRConrad wrote: post #122
goto11 wrote: post #116

Earlier quoted context omitted.

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 valu…

Logical, not physical, someone said: https://news.ycombinator.com/item?id=27569106 String is physical implementation. Logically, the domain is telephone number.

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.

Re: What does First Normal Form mean?

#124

Post by goto11 »

CRConrad wrote: post #120
goto11 wrote: post #119

Earlier quoted context omitted.

This is what the article discuss. 1NF is about eliminating nested tables, which is not supported by SQL. Dumping a list of phone numbers into a string does not violate 1NF because a string is still just a single value from the perspective of the database.

The article is wrong: There is no "from the perspective of the database" here. Normalisation is a logical-level process, not physical. What we're using the database to do is to store information about the real world. 1NF is about eliminating nested relations. A list of telephone numbers is a relation -- a one-row table, if you will. > Normalized relations, and the relationship between one normalized relation and anot…

> 1NF is about eliminating nested relations.

Absolutely agree, this is the definition of 1NF. So if you have a relation which has an attribute which has a domain which itself include relations - then you have a 1NF violation.

"From the perspective of the database" is perhaps too vague (although Codd uses almost the same phrasing), the question is if the value is atomic with regard to the relational operators (project, join etc.). Can you INSERT a tuple into a value? Then the value is a relation.

I get that at a certain abstraction level a comma separated string value could be considered a list or a relation. But then it would be at a different abstraction level than the one where the containing table is a relation. Because you can select rows and column from this outer relation using the relational operators, but you cant do the same for a relation encoded into a string value.

Re: What does First Normal Form mean?

#125

Post by goto11 »

CRConrad wrote: post #121
goto11 wrote: post #118

Earlier quoted context omitted.

> Surely "exactly one value" from the applicable domain of telephone numbers is a telephone number, not a comma-delimited list of telephone numbers. Yes certainly. But note that when we talk about comma-separated values, we are talking about multiple values encoded into a string and treated as a single string (or VARCHAR) value on the database level. In other words, the domain is the set of strings, not the set of ph…

And an RDBMS on-disk structure is just a binary blob, so let's have those as values within another RDBMS... No, that's still a whole fricking database. Just because it's encoded into a single binary blob doesn't change that. Neither does encoding a list of telephone numbers into a string change the fact that it's a list.

1NF is defined in terms of attribute domains on the logical model.

It all comes down to if the value is atomic with regard to relational algebra. Can you project attributes or insert tuples in the value using the standard relational operators? Then it is a relation, and you have a 1NF violation.

Post reply on HN