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

51–60 of 125 posts

Re: What does First Normal Form mean?

#51

Post by mulmen »

jameshart wrote: post #42
turbocon wrote: post #12

Earlier quoted context omitted.

It's rare in practice but occasionally rows have natural unique identifiers that are useable. The textbook case would be student IDs in a university database where each row corresponds to a single student. However some universities view the student id as sensitive information (kind of like a ssn in the US) and so in that scenario it should be aliased to some other key to prevent the student id from being present in m…

Student IDs are certainly not ‘natural’ keys. I am almost entirely convinced that there is actually no such thing as a natural key.

Natural keys are everywhere. They are just less interesting than the relationships. My name is a natural key. It uniquely identifies my name. The wheels come off if you expect it to uniquely identify me. But with natural keys that's never actually necessary.

When I buy a car they record my name, my date of birth, the time of the sale and some details about the car, such as the VIN, which is its name. That provides enough information to form a natural key to identify the sale.

At no point does a database actually have a concept of who I am really, only the relevant data points to satisfy its own relational model.

Any database that means to track information about people would be able to construct a natural key from my name, my mothers name and some information about my birth, such as the doctor, hospital and time.

Re: What does First Normal Form mean?

#52

Post by CharlesW »

jameshart wrote: post #42
turbocon wrote: post #12

Earlier quoted context omitted.

It's rare in practice but occasionally rows have natural unique identifiers that are useable. The textbook case would be student IDs in a university database where each row corresponds to a single student. However some universities view the student id as sensitive information (kind of like a ssn in the US) and so in that scenario it should be aliased to some other key to prevent the student id from being present in m…

Student IDs are certainly not ‘natural’ keys. I am almost entirely convinced that there is actually no such thing as a natural key.

> I am almost entirely convinced that there is actually no such thing as a natural key.

Yes! Or if it is now, there's a future scenario where that will have proven to have been a poor decision.

Re: What does First Normal Form mean?

#53

Post by crazygringo »

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.

Re: What does First Normal Form mean?

#54

Post by brazzy »

jrumbut wrote: post #43
mamcx wrote: post #26

Earlier quoted context omitted.

> when in every schema I have ever designed or seen, all rows get unique integer IDs anyway. This is artifact of 4 things: - Indexed Integer search are fast in RDBMS - Having an non-bussines related value for identify a row could save you from making triggers for updated in other relations if it change . This is the part a lot of folks missed: If you already have a natural PK and it not change, is wasteful add ANOTHE…

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 or dashes). So the legacy system also has the "published part number" as one or more separate fields.

This inconsistency makes me lean very strongly towards using a synthetic PK instead.

Re: What does First Normal Form mean?

#55

Post by wizzwizz4 »

derefr wrote: post #29

> A relation more or less correspond to a table "More or less", but mostly less. Views are relations. The rowsets returned by a SELECT query are relations. Etc. Without going into what relational algebra is about, I find it a lot more helpful to understand a "relation" like this: A relational database is like a Prolog interpreter: it's a thing that "knows" a bunch of "facts", because someone has "asserted" (declared…

> A stored procedure that takes a fathers(text, text) row, shouldn't necessarily be able to accept a mothers(text, text) row!

Of course, in the real world, you shouldn't make such a distinction. People are always messier than your database schema.

Re: What does First Normal Form mean?

#56

Post by uDontKnowMe »

ARandomerDude wrote: post #15
cyberbanjo wrote: post #7

Earlier quoted context omitted.

What about people with the same birthdays?

I think this was the parent's point. Since, as you point out, we we almost never use the data itself (e.g. date of birth) as the primary key, the parent is asking why is this a feature at all? Why not just mandate a uuid, number, etc. as the primary key?

Well it's easy enough to think of a situation where you would use a date as a primary key, for example time series data.

Re: What does First Normal Form mean?

#57

Post by wolfgang42 »

jameshart wrote: post #42
turbocon wrote: post #12

Earlier quoted context omitted.

It's rare in practice but occasionally rows have natural unique identifiers that are useable. The textbook case would be student IDs in a university database where each row corresponds to a single student. However some universities view the student id as sensitive information (kind of like a ssn in the US) and so in that scenario it should be aliased to some other key to prevent the student id from being present in m…

Student IDs are certainly not ‘natural’ keys. I am almost entirely convinced that there is actually no such thing as a natural key.

a "natural key" is frequently just a really foreign key in a database you and your org don't manage. — 'wweston, https://news.ycombinator.com/item?id=27349246

Re: What does First Normal Form mean?

#58

Post by brazzy »

whatshisface wrote: post #6

I have never understood why RDBs have such general concepts of primary keys, where you can for example let date of birth be a primary key, when in every schema I have ever designed or seen, all rows get unique integer IDs anyway.

Since when is "date of birth" a distinct type?

And "date" definitely does make sense as a primary key when you have a table that has conceptually at most one entry per day. For example, a travel journal. Or a time sheet application.

Re: What does First Normal Form mean?

#59

Post by gfody »

mamcx wrote: post #22
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…

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.

it's all a subtle misunderstanding of who's doing the considering and what's being considered. the author wants to say 1nf is of no concern to users of rdbms, that they can't actually violate 1nf because the system doesn't violate 1nf - however when users are considering normal form it's about the information (the phone numbers) not the data (a string) and in that sense the information is not normalized due to violating first normal form. a different audience, say the rdbms designers, can't have concerns about anything beyond the string - they don't care what you stash in there it's just data and in that sense the data is normalized but the information is not.

as an aside I think a lot of this sprouts from subtle ambiguity in the language. you really shouldn't just drop all articles and expect your english to still make sense!

Re: What does First Normal Form mean?

#60

Post by bob1029 »

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…

We deal with a lot of crap where a certain business entity has several different biz keys that can reasonably be used to uniquely identify the thing.

I would concur with adding a synthetic primary key, and maintaining all of the relevant business/domain keys on the same type.

The advantage of this approach is that you can still keep 100% correct relations between your internal types, even if the domain keying scheme is fucked up or otherwise broken per your original assumptions. It's really easy to add a new column to refine the additional domain keys. Fixing a bad PK and all the things that talk to it is much harder.

Post reply on HN