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

31–40 of 125 posts

Re: What does First Normal Form mean?

#31

Post by mmcgaha »

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.

I completely agree with you, yet I design most of my schemas with integer keys anyway. The only thing I do different than most folks is that I make a unique constraint/index for candidate keys (the proper primary key) because otherwise we have lost the ability to ensure data consistency.

As for why I use integer keys, it has always been the convention everywhere I go so I don't want to break convention.

Now the only convention that I would rather see would be to use GUIDs instead of integers because I hate that I can join an item table to an employee table and get results back.

Re: What does First Normal Form mean?

#32

Post by wwweston »

dnlhg wrote: post #16
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.

Codd was pretty adamant in his original papers that keys should be made up from data rather than a unique integer, and his example of converting from a hierarchical database schemas to a relational database schema shows how compound, natural keys are important to represent relationships. But we don’t usually do it that way today, partly because it isn’t that practical with our current databases and ORMs. It has some…

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.

And this is why I think it's probably dead. If the boring practicality of generated identifiers didn't do it, laws like the CCPA which are going to, because they recognized the potential of broad cross-operable data to be a liability and a hazard vs an asset.

Re: What does First Normal Form mean?

#33

Post by whatshisface »

dylan604 wrote: post #19
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.

How would DoB work as a primary key? A primary key is useless if not unique, right? And I know many people born on the same day that aren't even twins. Incrementing integers are nice and easy for humans to consume. Using GUIDs is far more portable than unique integers, but damn near impossible for humans to deal.

They wouldn't, so my point is why do RDBMSs support so many kinds of primary keys?

Re: What does First Normal Form mean?

#34

Post by whatshisface »

wwweston wrote: post #32
dnlhg wrote: post #16

Earlier quoted context omitted.

Codd was pretty adamant in his original papers that keys should be made up from data rather than a unique integer, and his example of converting from a hierarchical database schemas to a relational database schema shows how compound, natural keys are important to represent relationships. But we don’t usually do it that way today, partly because it isn’t that practical with our current databases and ORMs. It has some…

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.

Re: What does First Normal Form mean?

#35

Post by dylan604 »

whatshisface wrote: post #33
dylan604 wrote: post #19

Earlier quoted context omitted.

How would DoB work as a primary key? A primary key is useless if not unique, right? And I know many people born on the same day that aren't even twins. Incrementing integers are nice and easy for humans to consume. Using GUIDs is far more portable than unique integers, but damn near impossible for humans to deal.

They wouldn't, so my point is why do RDBMSs support so many kinds of primary keys?

Keys can span multiple columns, so DoB could be a differentiator if First, Last are also the same. I personally would never try to build a key like that though, but that's the only way I could see justifying its use.

If you can't see a why for uses of a field that is truly more unique, then I'd suggest you just need some more imagination. Strictly not allowing it seems to me to be short sighted just because someone had the ill conceived idea to use DoB as a primary key

Re: What does First Normal Form mean?

#36

Post by whatshisface »

mamcx wrote: post #26
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.

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

A time series database isn't keyed by day/month/year, it's typically keyed by monotonic timestamps.

Re: What does First Normal Form mean?

#37

Post by Kranar »

jraph wrote: post #11
cyberbanjo wrote: post #7

Earlier quoted context omitted.

What about people with the same birthdays?

What about them? Do they really exist, practically speaking? At a quick glance on my databases, I see nothing of the sort.

Are we seriously discussing whether in practice, two people can be born on the same day?

Re: What does First Normal Form mean?

#38

Post by hknapp »

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.

I worked at a place that used decimals that auto incremented by 2

Re: What does First Normal Form mean?

#39

Post by whatshisface »

dylan604 wrote: post #35
whatshisface wrote: post #33

Earlier quoted context omitted.

They wouldn't, so my point is why do RDBMSs support so many kinds of primary keys?

Keys can span multiple columns, so DoB could be a differentiator if First, Last are also the same. I personally would never try to build a key like that though, but that's the only way I could see justifying its use. If you can't see a why for uses of a field that is truly more unique, then I'd suggest you just need some more imagination. Strictly not allowing it seems to me to be short sighted just because someone h…

Can you think of any good use case for a non-arbitrary primary key? You are right that first name + last name + dob is not a good way to design the system. For example someone could differentiate their twin children only by their middle name. So, what's an example of a non-integer non-arbitrary primary key?

Re: What does First Normal Form mean?

#40

Post by whatshisface »

TeMPOraL wrote: post #14
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…

> 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 multiple tables as a foreign key. It is ironic, given that the whole reason to have a student ID is for it to be a primary key in some database somewhere.

Databases are vulnerable to a particular type of folkloric magic:

https://en.m.wikipedia.org/wiki/True_name

Post reply on HN