What does First Normal Form mean? • Hacker News Board

Re: What does First Normal Form mean?

#101

Post by goto11 » Sun, Jun 20, 2021, 10:50 AM UTC

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

Re: What does First Normal Form mean?

#102

Post by goto11 » Sun, Jun 20, 2021, 11:15 AM UTC

crazygringo wrote: post #68

zozbot234 wrote: post #62

Earlier quoted context omitted.

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

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

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

Re: What does First Normal Form mean?

#103

Post by goto11 » Sun, Jun 20, 2021, 11:32 AM UTC

blacktriangle wrote: post #25

mamcx wrote: post #22

Earlier quoted context omitted.

No, is not a contradiction. Is like say "Is wrong to store a large json in a cell in an array, that make it not an array". A relation is A VALUE. That is. The atomicity of the data INSIDE each "cell" is orthogonal to the fact the that relation is a relation. This is the point.

For even more fun mind bending, there's absolutely nothing that violates the relational model in having relation-valued attributes. SQL doesn't support that, but that doesn't mean its non-relational.

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

Re: What does First Normal Form mean?

#104

Post by goto11 » Sun, Jun 20, 2021, 11:34 AM UTC

dragonwriter wrote: post #78

gfody wrote: post #8

I think he contradicts himself and confuses the data with the information. I don't think delimited values representing a table in a column can be considered normalized. > 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 s…

> I think he contradicts himself and confuses the data with the information. I would say he is confusing the physical representation with the logical model.

Can you elaborate on why you think that? 1NF does not say anything about physical representation, it only talks about the logical model.

Re: What does First Normal Form mean?

#105

Post by blacktriangle » Sun, Jun 20, 2021, 1:17 PM UTC

goto11 wrote: post #103

blacktriangle wrote: post #25

Earlier quoted context omitted.

For even more fun mind bending, there's absolutely nothing that violates the relational model in having relation-valued attributes. SQL doesn't support that, but that doesn't mean its non-relational.

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 realization that leads you to potentially interesting projects like CJ Date's efforts to build truly relational databases.

Re: What does First Normal Form mean?

#106

Post by dragonwriter » Sun, Jun 20, 2021, 2:51 PM UTC

jameshart wrote: post #91

dragonwriter wrote: post #79

Earlier quoted context omitted.

Dates are natural keys in a calendar table. The composite of the foreign key values is a natural key in a join table expressing a unique relationship between entities in two or more other tables, perhaps with additional attributes, even if those values are surrogate keys in their own tables.

Dates aren’t natural. I know of at least three different date keys in different calendars that refer to the same calendar day - choosing to use one particular date standard to refer to days is choosing a pre-existing synthetic key, not a ‘natural’ one.

> I know of at least three different date keys in different calendars that refer to the same calendar day

Aside from the fact that you have very limited knowledge of calebdar systems, I’m not sure what this is supposed to prove. “Natural” in “natural key” doesn't mean “exists in nature outside of human invention”, it means “exists outside the database as part of the data in the data model, rather than being created solely to have a ubique key in the database ubder design".

The date in whatever particular calendar system is used in the data model is a natural key for a calendar table.

Re: What does First Normal Form mean?

#107

Post by goto11 » Sun, Jun 20, 2021, 5:54 PM UTC

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…

But isn't Codd's relational algebra based on 1NF relations? The point of 1NF is (among other things) to make the algebra simpler because you don't need to handle nested relations. A database built on non-1NF relations would be very different from Codd's vision.

Re: What does First Normal Form mean?

#108

Post by goto11 » Mon, Jun 21, 2021, 7:47 AM UTC

painchoc wrote: post #49

What about NULLs ? Is it allowed or not ? I recently discovered on Wikipedia https://en.wikipedia.org/wiki/First_normal_form that it should not be.

They should not be allowed according to C.F.Date, but this is quite a controversial opinion. E.F.Codd who invented the relational model thinks nulls are fine.

Re: What does First Normal Form mean?

#109

Post by goto11 » Mon, Jun 21, 2021, 7:51 AM UTC

jameshart wrote: post #97

mulmen wrote: post #96

Earlier quoted context omitted.

Great! Then if they are unambiguous they can be inserted directly or disambiguated and then inserted. At query time any format can be used to represent them to a user.

Ah, so you're suggesting instead of storing 'what a person calls this date' as the key, you instead convert it to some underlying representation that you can then choose to present in different calendars - like a UNIX epoch-relative offset or something. Congratulations, you just created another synthetic key for describing dates.

That is not what synthetic key means. A synthetic key is a key which does not carry any information, like an auto-incremented integer. A date is a natural key, regardless of the underlying representation.

Re: What does First Normal Form mean?

#110

Post by goto11 » Tue, Jun 22, 2021, 11:43 AM UTC

mannykannot wrote: post #67

zozbot234 wrote: post #62

Earlier quoted context omitted.

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

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