You don’t need to store an IP in a different table as ID/IP, IP can be represented as 128 bit integer. A small “mistake” I’ve made before that makes some things a little more annoying - not that it’s particularly important
A terrible schema from a clueless programmer
351–360 of 493 posts
Re: A terrible schema from a clueless programmer
#352Earlier quoted context omitted.
I don't buy it. If the only point was managing rookie errors, etc., the blog post shouldn't have been 15~ paragraphs of technical discussion and 2 paragraphs at the bottom of "my point is ..." You can't advocate that people just ignore 80% of the article because they're "not the point of the post." I'm sure a good chunk of tech workers are worried that when an influential blogger writes something like this a couple h…
It's a narrative. A story. She told it in that way to lure people who get hooked on technical discussion, and then make the point that they probably would have missed or ignored. Without a narrative example, she could have just said "I made mistakes too, live and learn" but she chose to provide an example before presenting the thesis. And hey, what's so bad about people learning about 3NF? Are you not supposed to kno…
The problem with her point is that it doesn't go anywhere. Mentorship is obviously good and ageism is obviously bad, and she doesn't have specific suggestions on how to solve any of the problems, so the hook is the most substantial thing to talk about.
Re: A terrible schema from a clueless programmer
#353Feels like you could just concatenate and hash the 4 values with MD5 and store the hash and time. Edit: I guess concatenate with a delimiter if you're worried about false positives with the concat. But it does read like a cache of "I've seen this before" . Doing it this way would be compact and indexed well. MD5 was fast in 2002, and you could just use a CRC instead if it weren't. I suppose you lose some operational…
For the 2021 version, you'd just generate a bloom filter/cuckoo hash from all the data gathered by your spamhaus database periodically. Make a separate one for each value in your tuple and your score would be the number of the sub-hashes that matched.
“Periodically” here would have to be quite frequent, as you have to rebuild the database before the next retry.
Re: A terrible schema from a clueless programmer
#354People in the comments are getting (rightfully) outraged about the poor understanding of indexing, but I'm a little surprised that everyone here doesn't seem to understand normalization either. The original schema is perfectly normalized and is already in 3NF: none of the columns shown has a dependence on any of the other columns outside of the primary key (in other words, if you knew eg the values of the ip, helo, a…
>>> 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. This might not be important in the spam-graylisting us…
>> 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.
Re: A terrible schema from a clueless programmer
#355Earlier quoted context omitted.
If even someone with Rachel's level of experience still doesn't know all the minutiae of database optimization, I think that just amplifies her point about the importance of mentoring novices.
I recall reading a rant on another site about someone so upset they had to deal with clueless noobs at work. They then went on to list the errors that this brand new entry level employee had made when writing ... an authentication system ... I was more than a little shocked when I realized they were serious and hadn't realized the issue was sending the new entry level guy to do that job alone.
I would expect most seniors to want a set of eyes across their work doing any form of authk or z. If only to share blame :p
Re: A terrible schema from a clueless programmer
#356Earlier quoted context omitted.
I don't buy it. If the only point was managing rookie errors, etc., the blog post shouldn't have been 15~ paragraphs of technical discussion and 2 paragraphs at the bottom of "my point is ..." You can't advocate that people just ignore 80% of the article because they're "not the point of the post." I'm sure a good chunk of tech workers are worried that when an influential blogger writes something like this a couple h…
It's a narrative. A story. She told it in that way to lure people who get hooked on technical discussion, and then make the point that they probably would have missed or ignored. Without a narrative example, she could have just said "I made mistakes too, live and learn" but she chose to provide an example before presenting the thesis. And hey, what's so bad about people learning about 3NF? Are you not supposed to kno…
If she told it to lure people who get hooked on technical discussion, then it’s not fair for you to complain about said people analyzing said technical discussion.
Re: A terrible schema from a clueless programmer
#357I once ended up doing 'rm -rf *' by mistake - https://ashishb.net/tech/my-rm-rf-moment/
Re: A terrible schema from a clueless programmer
#358The ending is the most important part. > Now, what do you suppose happened to that clueless programmer who didn't know anything about foreign key relationships? > Well, that's easy. She just wrote this post for you. That's right, I was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue. > My point is: E…
> My point is: EVERYONE goes through this
A lot of the basic issues beginners go through can be mitigated by paying attention in class (and/or having formal education in the first place).
I’ll grant that there are other, similar, things that everyone will go through though.
My own was manually writing an ‘implode’ function in Javascript because I didn’t know ‘join’ existed.
Re: A terrible schema from a clueless programmer
#359[deleted]
Re: A terrible schema from a clueless programmer
#360Related to database indexes, but not the post: a busted database index brought down ticket sales of the 2008 Olympics Games. This was the first time regular people could go buy tickets for events & they had been lining up overnight at Bank of China locations through the country. We were down for over a day before we called it off. Apparently this led to minor upheaval at several locations in Beijing & riot police wer…
Can't imagine the pain to even discover this problem. It normally takes me a long time to be like, "maybe I didn't make a mistake, and I have a third party bug?". Third party bugs are always the most annoying to me, usually the longest to diagnose and then ultimately I have to just tell my boss I can't do anything to fix it, but hopefully I can find a way to avoid it.
Fork and fix? I’ve personally been pleasantly surprised by how fast maintainers will merge a PR I submit.
If it causes an issue with our own systems it also gives me a very good justification for doing it.