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

91–100 of 125 posts

Re: What does First Normal Form mean?

#91

Post by jameshart »

dragonwriter wrote: post #79
jameshart wrote: post #42

Earlier quoted context omitted.

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

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.

Re: What does First Normal Form mean?

#92

Post by jameshart »

mulmen wrote: post #51
jameshart wrote: post #42

Earlier quoted context omitted.

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

(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 keys from some other authority for identifying things. VINs obviously are synthetic identifiers, but so are names, dates, hospital addresses.

As you rightly say, a string is a natural key for itself; likewise a number.

The only other ‘natural key’ I can think of is atomic number for identifying chemical elements.

Re: What does First Normal Form mean?

#93

Post by mulmen »

jameshart wrote: post #92
mulmen wrote: post #51

Earlier quoted context omitted.

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

(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

Re: What does First Normal Form mean?

#94

Post by mulmen »

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.

Dates are natural keys.

You seem to be conflating format and value. 1,234,567 and 1234567 are the same number. 19-Jun-21 and 2021-06-19 are the same date. Databases already handle this.

Re: What does First Normal Form mean?

#95

Post by jameshart »

mulmen wrote: post #94
jameshart wrote: post #91

Earlier quoted context omitted.

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.

Dates are natural keys. You seem to be conflating format and value. 1,234,567 and 1234567 are the same number. 19-Jun-21 and 2021-06-19 are the same date. Databases already handle this.

19 June 2021 is also known as:

Dhuʻl-Qiʻdah 9, 1442 AH

9 Tamuz 5781

Re: What does First Normal Form mean?

#96

Post by mulmen »

jameshart wrote: post #95
mulmen wrote: post #94

Earlier quoted context omitted.

Dates are natural keys. You seem to be conflating format and value. 1,234,567 and 1234567 are the same number. 19-Jun-21 and 2021-06-19 are the same date. Databases already handle this.

19 June 2021 is also known as: Dhuʻl-Qiʻdah 9, 1442 AH 9 Tamuz 5781

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.

Re: What does First Normal Form mean?

#97

Post by jameshart »

mulmen wrote: post #96
jameshart wrote: post #95

Earlier quoted context omitted.

19 June 2021 is also known as: Dhuʻl-Qiʻdah 9, 1442 AH 9 Tamuz 5781

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.

Re: What does First Normal Form mean?

#98

Post by [deleted] »

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.

[deleted]

Re: What does First Normal Form mean?

#99

Post by mulmen »

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.

The date is a natural key. The format string is only a representation. Implementation details are irrelevant to the definition of a natural key.

Re: What does First Normal Form mean?

#100

Post by denton-scratch »

mulmen wrote: post #51
jameshart wrote: post #42

Earlier quoted context omitted.

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

Probably not unique, though. Well, yours might be; but in general, a person's name isn't a reliable unique identifier. Nor anyone's name. Even if you have a timestramp, you can't be sure it's unique.
Post reply on HN