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

41–50 of 125 posts

Re: What does First Normal Form mean?

#41

Post by mamcx »

whatshisface wrote: post #36
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…

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

Yes, that is even a more narrowing detail. (people that use a "time-series" rarely need that high of granularity!)

But the question was about wondering "why exist the support for PK that are not integers/guis, like dates"?.

To be able to model a lot of things for what in the past (before RDBMS) and today (for upstarts engines) you will be tempted to add another data store (that rarely is that necessary).

I know by a fact that some times is because people not realize you DON'T need to constrain your db to things like "only integers pks!"!

P.D: I work in the enterprise sector. Is fun when something is composed of a cluster of disparate things purely by misunderstanding you could have solved it with a simple db, well done. Contrary to belief, a lot of my customers, their largest databases are 1-10GB each. A rdbms can happily go much much larger than that, if you give them a little love...

Re: What does First Normal Form mean?

#42

Post by jameshart »

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…

Student IDs are certainly not ‘natural’ keys.

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

Re: What does First Normal Form mean?

#43

Post by jrumbut »

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…

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.

Re: What does First Normal Form mean?

#44

Post by fuckf4ce »

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.

Relation valued attributes are precisely the antithesis of first normal form. That's like exactly what first normal form is about.

Re: What does First Normal Form mean?

#45

Post by dragonwriter »

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

Because sequential integer surrogate keys aren't always a good idea, e.g.:

1. Certain logically-unique join tables, where the foreign keys to the joined tables form an obvious composite primary key.

2. Cases with natural primary keys for basic data; A date might not be a good primary key if its the DoB in a table of people, but its an excellent primary key in a calendar table.

3. Even when you need surrogate keys, sometimes you need them to be able to be generated in a distributed manner, so you want something like UUID/ULID instead of sequential integers.

Re: What does First Normal Form mean?

#46

Post by projektfu »

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.

In one sense, the unique integer is often not real data. It’s synthetic and has no meaning outside the application.

I find it easier to understand when I think that Codd and other database pioneers were looking at creating universal databases, not the application-specific ones we tend to use.

Furthermore, the use of auto numbering tends to make the queries easier to write but the tables less meaningful. For example, INVOICE might have a sequential invoice id that is real information, as well as a reference to customer, billing address, shipping address, etc. INVOICE_ITEM will have a foreign key to an invoice, unique number, position, stock item id (second foreign key), description, etc. PACKING_LIST might have a foreign key to INVOICE_ITEM. In my opinion, I prefer to see INVOICE_ID as part of the primary key of INVOICE_ITEM. I like being able to see the relationship in foreign keys and things like that.

These things can be a heated topic and other designers probably think I also like to murder kittens. But the data layout is, I think, what Codd was going for.

Re: What does First Normal Form mean?

#47

Post by fuckf4ce »

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.

Avoiding "natural keys" has little to do with relational databases or the model. Consider that the Social Security Number far predates relational databases.

The reason for using surrogate keys is not some great conspiracy or mass incompetence. Simply, there are very few cases where natural keys work well.

Good keys are generally stable, globally unique, not too big, and it turns out that these qualities are extremely rare in a natural key in practice. Furthermore, composite keys over more than 2 or 3 attributes become terribly unwieldy (and intuitively, composite key size and stability are inversely related).

What is a good natural key for an individual? There are none! The SSA figured this out in the 20s, this was not a modern tech problem.

Even social security numbers can't be stable, but they are far better than things like names or addresses because they change for only a handful of reasons.

That said, a lot of database designs should use composite primary keys. You can in part thank early MySQL which started the ball rolling on generally shitty database design in the late 90s.

Re: What does First Normal Form mean?

#48

Post by dylan604 »

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?

Not sure exactly what you mean by non-arbitrary primary key, but...

GUID/UUID

FeatureName/ReleaseYear or Series/Season/Episode

email

phone-number

SSN

driverslicense#/State

Re: What does First Normal Form mean?

#50

Post by brazzy »

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.

You're not really addressing the point of the comment you are replying to, which is about what constitutes a normalized relation, not what constitutes a relation.

And I agree, TFA is just flat out wrong.

A relation Manager with a CSV-valued column SubordinateIds or columns named "Subordinate1Id" through "Subordinate9Id" is most definitely not normalized because those are in effect a (bad) simulation of hierarchical-database-style repeating groups. That they have distinct names (or positions in the CSV list) is an artifact of the representation and not really part of the data model.

Post reply on HN