4 ms·

>> Mapping the string "address1@foo.bar" to a new value "id1" has no effect on the relations in your table. >How do you mean? If id1 is unique on table A, and

>> Mapping the string "address1@foo.bar" to a new value "id1" has no effect on the relations in your table. >How do you mean? If id1 is unique on table A, and table B has a foreign key dependency on A.id, then yeah you still have id1 in twenty locations but it's normalized in that altering the referenced table once will alter the joined value in all twenty cases. Ok, but that's not what normalization means. If you have a table as described in the article that looks like: foo | bar | baz ------------------ foo1 | bar1 | baz1 foo2 | bar2 | baz2 foo3 | bar3 | baz3 foo4 | bar1 | baz2 foo3 | bar1 | baz4 and then you say "ok, bar1 is now actually called id1", you now have a table foo | bar | baz ------------------ foo1 | id1 | baz1 foo2 | bar2 | baz2 foo3 | bar3 | baz3 foo4 | id1 | baz2 foo3 | id1 | baz4 you haven't actually changed anything about the relationships in this data. You've just renamed one of your values. This is really a form of compression, not normalization. Normalization is fundamentally about the constraints on data and how they are codified. If you took the author's schema and added a new column for the country where the ip address is located in (so the columns are now ip, helo, from, to, and country), then the table is no longer normalized because there is an implicit relationship between ip and country--if ip 1.2.3.4 is located in the USA, every row with 1.2.3.4 as the ip must have USA as the country. If you know the IP for a row, you know its country. This is what 3NF is about. Here you'd be able to represent invalid data by inserting a row with 1.2.3.4 and a non-US country, and you normalize this schema by adding a new table mapping IP to country. But none of that is what's going on in the article. The author described several fields that have no relationship at all between them--IP is assumed to be completely independent of helo, from address, and to address. And the second schema they propose is in no way "normalizing" anything. The four new tables don't establish any relationship between any of the data. >This might not be important in the spam-graylisting use case, and very narrowly it might be 3NF as originally written, but it certainly wouldn't be if there were any other data attached to each email address, such as a weighting value. It's not "very narrowly" 3NF. It's pretty clear-cut! A lot of commenters here are referring to the second schema as the "normalized" one and to me that betrays a fundamental misunderstanding of what the term even means. And sure, if you had a different schema that wasn't normalized, then it wouldn't be normalized, but that's not what's in the article.

Aeolun 5y ago
I think I’ve determined very long ago that the only normal form worth keeping in mind is the Boyce-Codd normal form. While you may technically be correct, I think there’s few people that think of anything else when talking database normalization. That said, I cannot quickly figure out if it’s actually true here.
- alksjoflkajsom 5y ago
  Okay, this will be my last comment in this thread because I don't have any new way of presenting this and checking this is not a good use of my time. This schema is also in Boyce-Codd normal form. It's normalized in every usual sense of the word. Trivially so, even. It's not a question of being "technically" correct. If you think the second schema is more normalized than the first one, you need to re-evaluate your mental model of what normalization means. That's all there is to it.
noduerme 5y ago
Your point is well taken. In the bare description provided for the first schema in the article, the data is already normalized, and all that can be achieved is compression.
yobbo 5y ago
> This is really a form of compression, not normalization. First of all, the index hashes of the emails depend on the email strings, hence the indexed original schema is not normalised. Secondly, it would not effectively be compression unless there were in fact dependencies in the data. But we can make many fair assumptions about the statistical dependencies. For example, certain emails/ips occur together more often than others, and so on. In so far as our assumptions of these dependencies are correct, normalisation gives us all the usual benefits.

Aeolun 5y ago

I think I’ve determined very long ago that the only normal form worth keeping in mind is the Boyce-Codd normal form. While you may technically be correct, I think there’s few people that think of anything else when talking database normalization. That said, I cannot quickly figure out if it’s actually true here.

alksjoflkajsom 5y ago
Okay, this will be my last comment in this thread because I don't have any new way of presenting this and checking this is not a good use of my time. This schema is also in Boyce-Codd normal form. It's normalized in every usual sense of the word. Trivially so, even. It's not a question of being "technically" correct. If you think the second schema is more normalized than the first one, you need to re-evaluate your mental model of what normalization means. That's all there is to it.

noduerme 5y ago

Your point is well taken. In the bare description provided for the first schema in the article, the data is already normalized, and all that can be achieved is compression.

yobbo 5y ago

> This is really a form of compression, not normalization. First of all, the index hashes of the emails depend on the email strings, hence the indexed original schema is not normalised. Secondly, it would not effectively be compression unless there were in fact dependencies in the data. But we can make many fair assumptions about the statistical dependencies. For example, certain emails/ips occur together more often than others, and so on. In so far as our assumptions of these dependencies are correct, normalisation gives us all the usual benefits.