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

111–120 of 125 posts

Re: What does First Normal Form mean?

#111

Post by CRConrad »

goto11 wrote: post #110
mannykannot wrote: post #67

Earlier quoted context omitted.

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

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

> 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 [@ "Satisfying 1NF"]

A set of telephone numbers isn't "a value"; a telephone number is. (More on that further up the WP page.)

Re: What does First Normal Form mean?

#112

Post by CRConrad »

goto11 wrote: post #101
crazygringo wrote: post #53

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…

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

> 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 , 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.

Re: What does First Normal Form mean?

#113

Post by CRConrad »

blacktriangle wrote: post #105
goto11 wrote: post #103

Earlier quoted context omitted.

1NF as defined by E.F.Codd literally does not allow relation-values attributes.

Which is why it's worth even specifying 1NF. That doesn't mean that the relational algebra does not allow for them. I think the reason people even ask questions like what does 1NF even mean is because they're not learning relational algebra, they are learning SQL. So if every table is in 1NF by default, it begs the question what is the point of 1NF? And the point is that the relational algebra is bigger than SQL, a r…

> So if every table is in 1NF by default, it begs the question what is the point of 1NF?

Your premise isn't fulfilled: Not every SQL table is in 1NF by default, it's quite easy to fuck that up. As illustrated by the whole "list of telephone numbers in a single column" point discussed here.

Re: What does First Normal Form mean?

#114

Post by CRConrad »

brazzy wrote: post #54
jrumbut wrote: post #43

Earlier quoted context omitted.

It's wonderful what you can gain in performance, correctness, and storage efficiency from finding the real primary key from the data model itself. Maybe not worth the intensive design work for many tables, and it can be a risk to get it wrong, but it's cool when it does work.

Yeah, I'm currently facing exactly that dilemma. I have to model a database of machine parts. There is a natural PK in the form of the supplier's parts number, but the legacy system (which uses that as PK) notes that it is specifically the parts number as used in the supplier's ERP system , but suppliers often use differently formatted parts numers in publications such as catalogs (adding or removing spaces, slashes…

> There is a natural PK in the form of the supplier's parts number, but ...

Or even better: Then the supplier revamps their parts numbering scheme, and every product gets a new number.

Re: What does First Normal Form mean?

#115

Post by CRConrad »

whatshisface wrote: post #34
wwweston wrote: post #32

Earlier quoted context omitted.

Yeah, I think the early proponents of relational models had a lot to do with setting the culture here. I suspect that some of this has to do with an insight I recently discovered: a "natural" key is in some sense a key that's so foreign it's in a "database" you don't control. And if you establish a culture that converges on natural keys, you may end up with more easily cross-operable databases/collections as an asset…

Those "natural" databases as a rule are going to be full of duplicates and sometimes even inconsistencies, hence the need for IDs that only exist for the purpose of expressing relationships within your own database. Basically, pointers.

https://news.ycombinator.com/item?id=27658993

Re: What does First Normal Form mean?

#116

Post by goto11 »

CRConrad wrote: post #111
goto11 wrote: post #110

Earlier quoted context omitted.

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

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

Re: What does First Normal Form mean?

#117

Post by CRConrad »

mulmen wrote: post #93
jameshart wrote: post #92

Earlier quoted context omitted.

(Interesting cultural assumption there that people are born in hospitals under the supervision of doctors) The thing is to qualify as a natural key I think it has to be precisely recoverable just from first principles wherever you encounter the artifact it references. The birth details aren’t inherent to the person, they’re datapoints about them. All the identifiers you propose to make up mnatural’ keys are synthetic…

I said a database that tracks people could identify me using information about my birth. Not that everyone is born in a hospital or by a doctor. By your definition there are no natural keys. But since that definition isn't useful we instead use one that is. The natural key is the identifier by which the thing is already known. https://en.wikipedia.org/wiki/Natural_key

> I said a database that tracks people could identify me using information about my birth.

And your name, you said.

> The natural key is the identifier by which the thing is already known.

And then you change your name.

Re: What does First Normal Form mean?

#118

Post by goto11 »

CRConrad wrote: post #112
goto11 wrote: post #101

Earlier quoted context omitted.

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

> 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 phone numbers.

If a database has a phone number type, it can use that as domain and ensure that an attribute can only contain a single phone number.

Re: What does First Normal Form mean?

#119

Post by goto11 »

CRConrad wrote: post #113
blacktriangle wrote: post #105

Earlier quoted context omitted.

Which is why it's worth even specifying 1NF. That doesn't mean that the relational algebra does not allow for them. I think the reason people even ask questions like what does 1NF even mean is because they're not learning relational algebra, they are learning SQL. So if every table is in 1NF by default, it begs the question what is the point of 1NF? And the point is that the relational algebra is bigger than SQL, a r…

> So if every table is in 1NF by default, it begs the question what is the point of 1NF? Your premise isn't fulfilled: Not every SQL table is in 1NF by default, it's quite easy to fuck that up. As illustrated by the whole "list of telephone numbers in a single column" point discussed here.

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.

Re: What does First Normal Form mean?

#120

Post by CRConrad »

goto11 wrote: post #119
CRConrad wrote: post #113

Earlier quoted context omitted.

> So if every table is in 1NF by default, it begs the question what is the point of 1NF? Your premise isn't fulfilled: Not every SQL table is in 1NF by default, it's quite easy to fuck that up. As illustrated by the whole "list of telephone numbers in a single column" point discussed here.

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 another, mirror real-world concepts and their interrelationships. -- https://en.wikipedia.org/wiki/Database_normalization#Minimiz...

In the real world, a phone call is to a single phone number, not to a list of them.

> To satisfy First normal form, each column of a table must have a single value. Columns which contain sets of values or nested records are not allowed. -- https://en.wikipedia.org/wiki/Database_normalization#Satisfy...

A list of telephone numbers is a set of values, not a single value. Several strings "in one".

Post reply on HN