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

81–90 of 125 posts

Re: What does First Normal Form mean?

#81

Post by lisper »

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…

> Nobody's saying strings should be split into individual characters, or small JPEG thumbnail blobs ought to be decomposed into fields for each header.

That could actually be useful if you want to be able to, for example, find all the jpegs with dimensions in a certain range.

Re: What does First Normal Form mean?

#82

Post by kbelder »

hknapp wrote: post #38
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

Smart. That allows them to insert new rows without messing up the ordering.

Re: What does First Normal Form mean?

#83

Post by bart_spoon »

turbocon wrote: post #12
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.

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…

I think a natural key that I’ve encountered is a database containing professional NBA data. Teams never play more than once per day, and there are only two teams in the game, so a natural (composite) key for games is simply the date of the game and the two teams that played. You can create a “game ID” if you wish, but you aren’t actually doing anything but adding a column of redundant information.

Re: What does First Normal Form mean?

#84

Post by bart_spoon »

whatshisface wrote: post #39
dylan604 wrote: post #35

Earlier quoted context omitted.

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?

I stated this elsewhere, but with some sports data, you don’t gain anything with a uniquely generated game ID. NBA teams will only play one game at most per day, and only play one other team, so if you make a key of the date and the teams playing, you uniquely identify every event. You can create an arbitrary ID for a game if you want, but it’s not actually giving you information you don’t have, and therefore is entirely redundant.

Re: What does First Normal Form mean?

#85

Post by derefr »

wizzwizz4 wrote: post #55
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.

It wasn't the best example. Here's a better one:

    CREATE TABLE siblings(personA text, personB text);
    CREATE TABLE owners_pets(owner text, pet text);
Under structural identity, both of these would be just considered to be (text, text) tuples.

Even in the grittiest of real-worlds, I think you'd agree that there's no place where you'd want something that explicitly says it operates on siblings rows, to implicitly accept and operate upon an owners_pets row instead. (They aren't even the same shape of relationship. One's symmetric, while the other's directed!)

And, to be clear, if you just wanted function on a generic (text, text) tuple-type, you could declare such a type, and explicitly cast your other types to it to use such a function on them. So no, that's not a good reason.

-----

Mind you, in some places in SQL, structural identity is used. Mostly when you explicitly "ask for it" to happen, by making use of some destructuring or 'restructuring' operator. Even when it makes no sense!

    -- ridiculous, but legal
    SELECT * from siblings
    UNION
    SELECT * FROM owners_pets
This is mostly because it'd be really inconvenient for the "temporary relation types of projected result-sets" case if UNION didn't use structural identity. It'd be the same sort of "now forced to give all your temporaries names" issue that afflicts Java's closures under checked exceptions, and Rust's lifetime annotations under lexical-capturing garbage-collected self-terminating actors.

Re: What does First Normal Form mean?

#86

Post by ipaddr »

whatshisface wrote: post #39
dylan604 wrote: post #35

Earlier quoted context omitted.

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?

Using a generated pagename as a key.

You create a cms. You store each page with a 16 character name as the key and you use that as the url/pagename.

What about storing the ip for login attempts. You store the ip and date and use that information to rate limit. The ip is the primary id.

Password reset, you store the unique code you sent in the email as the key.

Country codes. The 2 or 3 short name makes as a key over a number if you want to limit joins.

Bitcoin key.

Anything token, key related, anywhere where you want to enforce no duplicates like a ssn.

Re: What does First Normal Form mean?

#87

Post by ipaddr »

jcranmer wrote: post #21
jraph wrote: post #11

Earlier quoted context omitted.

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

I share the same name and birthdate as another individual. It has actually caused me to be held up at the border before.

But not birth time and birth location.

Re: What does First Normal Form mean?

#88

Post by brantam »

whatshisface wrote: post #39
dylan604 wrote: post #35

Earlier quoted context omitted.

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?

> Can you think of any good use case for a non-arbitrary primary key?

Login names; Vehicle registration numbers; flight numbers; domain names; file names; ip addresses. Keys are used in those cases where the enforcement of uniqueness is important for data integrity and identification purposes.

Re: What does First Normal Form mean?

#89

Post by goto11 »

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 don't think delimited values representing a table in a column can be considered normalized.

The question is what "normalized" means. It does not mean "best practice data modelling" which is a much broader subject.

The article does not say delimited values are good design.

Re: What does First Normal Form mean?

#90

Post by goto11 »

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…

Codd actually use a synthetic identifier for employee ID in an example in the original paper. So I don't think he is against synthetic identifiers in principle. But if a natural key exist of course a synthetic key need not be introduced.
Post reply on HN