Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A terrible schema from a clueless programmer

rachelbythebay.com

Post reply on HN
Print view View original

431–440 of 493 posts

Re: A terrible schema from a clueless programmer

#431

Post by miyuru »

_3u10 wrote: post #396
ttfkam wrote: post #392

Earlier quoted context omitted.

IP should NOT be an integer. IPv6 does not fit in a database integer. MySQL has functions to convert to bytes. Even better, use a database with a proper inet datatype. That way you get correctness, space efficiency, and ability to intelligently index.

In 2002 it definitely should have been an integer. In 2021 I’d recommend just turning off the IPv6 allocation or deleting it from DNS like this site does.

> In 2021 I’d recommend just turning off the IPv6 allocation or deleting it from DNS like this site does.

This is the funniest IPv6 excuse I've heard on HN.

https://ipv6excuses.com/

Re: A terrible schema from a clueless programmer

#432

Post by MarkSweep »

goto11 wrote: post #430
damagednoob wrote: post #261

Earlier quoted context omitted.

Yes and no and this post highlights a subtle issue with mentorship (which I think is important): Technology does not stand still. What was true in 2002, might not be true today. While adopting the naïve approach was detrimental back then, today databases recognise that this happens and provide easy workarounds to get you out of trouble that didn't exist back then. I've experienced this just by switching languages. C#…

Totally off topic I know, but what is the concern with String.Concat ?

If you repeated use concat to build up a string, the amount of time grows exponentially. This is because the string I copied each time you concat. Note that the + operator on strings gets turned into a call to concat.

https://docs.microsoft.com/troubleshoot/dotnet/csharp/string...

Re: A terrible schema from a clueless programmer

#433

Post by goto11 »

MarkSweep wrote: post #432
goto11 wrote: post #430

Earlier quoted context omitted.

Totally off topic I know, but what is the concern with String.Concat ?

If you repeated use concat to build up a string, the amount of time grows exponentially. This is because the string I copied each time you concat. Note that the + operator on strings gets turned into a call to concat. https://docs.microsoft.com/troubleshoot/dotnet/csharp/string...

I was wondering about it in the context of "yet, other languages would do the right thing by default". Repeatedly concatenating to the same string (as opposed to concatenating an array of strings in one go) would be slow in any language I know of, unless you allocate a larger buffer up front, which is what StringBuilder does.

Some languages have mutable strings, but you would still need to allocate a sufficiently larger buffer if you want to add strings in a loop.

Re: A terrible schema from a clueless programmer

#434

Post by twic »

Brian_K_White wrote: post #252
alksjoflkajsom wrote: post #224

Earlier quoted context omitted.

You might be right (I don't actually know what a HELO string is, I don't know anything about SMTP :). I was just going off how the author presented the data, as a tuple of four completely independent things. Of course the main point still stands, that the two schemas are exactly as normalized as each other. Edit: rereading the original post, the author mentions that "they forged...the HELO"--so perhaps there was inde…

I do know about smtp and you were right regardless, because the author was talking about 4 database fields, not smtp. The details of smtp are irrelevant.

Normalisation depends on the semantics of the data, and so the details of SMTP are very much relevant.

Re: A terrible schema from a clueless programmer

#435

Post by isolli »

path411 wrote: post #276
wdr1 wrote: post #109

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

Imagine having to diagnose a bug in the processor!

http://gallium.inria.fr/blog/intel-skylake-bug/

"More experienced programmers know very well that the bug is generally in their code: occasionally in third-party libraries; very rarely in system libraries; exceedingly rarely in the compiler; and never in the processor."

Re: A terrible schema from a clueless programmer

#436

Post by teh_klev »

ericbarrett wrote: post #245
junon wrote: post #34

Earlier quoted context omitted.

Indexing existed 15 years ago. The article never mentions why indexing didn't solve this problem. Super weird take on the author's part...

Early InnoDB* had pretty strict limits on varchar indexes and was not the most efficient. I don't remember the details but it's entirely possible the single-table format Rachel described ran head on into those limitations. Also remember indexes take space, and if you index all your text columns you'll balloon your DB size; and this was 2002, when that mattered a lot more even for text. Indexes also add write and comp…

> using MyISAM was considered the #1 newbie MySQL mistake back then, and it happened all the time.

The author mentions that these events took place in 2002. At that time the site was likely still running MySQL 3 and InnoDB was very new and considered experimental. Sure MySQL 4.0 had shipped in 2002 and InnoDB was a first class citizen, but back then upgrades from one version of MySQL to another weren't trivial tasks. You also tended to wait until the .1 release before making that leap.

So in fairness for the folks who originally set up that database MyISAM was likely their only realistic option.

I looked after and managed a fleet of MySQL servers from back then and for many years afterwards and even then it wasn't until MySQL 5 that we fully put our trust in InnoDB.

Re: A terrible schema from a clueless programmer

#437

Post by FlyingAvatar »

tomsmeding wrote: post #3

Why exactly would it be so bad to just put a suitable index on the table containing strings? The time complexity of the resulting search would be the same, so I assume there will be some constant factor slowdowns. Is it that indices over string fields are stored inefficiently on disk? (If so, can that not be fixed in the db engine directly?) Or is this fine today but wasn't fine 15 years ago?

I had the same initial reaction in reading the post. My assumption was that indexing would have sufficiently sped up the query speed problem.

However, normalizing the fields that contain repetitive data into separate tables could create significant space savings since the full text for each column would not need to stored for each row. Instead of 20-30 bytes per email address, a 4 byte (assuming 32-bit era) OID is stored in its place.

It's pretty easy to imagine how quickly the savings would add up, and that it would be very helpful in the era before SSDs or even 1TB HDDs existed.

Re: A terrible schema from a clueless programmer

#438

Post by dghf »

gavinray wrote: post #88
mewpmewp2 wrote: post #78

Earlier quoted context omitted.

I think if anything, all of it could've been put into a single indexed column since the query was AND ... AND ... not OR. So you could've had a indexed column of "fingerprint", like ip1_blahblah_evil@spammer.somewhere_victim1@our.domain And indexed this, with only single WHERE in the query. I don't understand at all how multiple tables thing would help compared to indices, and the whole post seemed kind of crazy to m…

This has been the advice given to me by Postgres experts in a similar scenario: "If you want to efficiently fuzzy-search through a combination of firstname + lastname + (etc), it's faster to make a generated column which concatenates them and index the generated column and do a text search on that." (Doesn't have to be fuzzy-searching, but just a search in general, as there's a single column to scan per row rather th…

>I'm pretty sure that the degree of normalization given in the end goes also well beyond 3rd-Normal-Form.

> I think that is 5th Normal Form/6th Normal Form or so, almost as extreme as you can get

The original schema is also in 5NF, unless there are constraints we aren't privy too.

5NF means you can't decompose a table into smaller tables without loss of information, unless each smaller table has a unique key in common with the original table (in formal terms, no non-trivial join dependencies except for those implied by the candidate key(s)).

The original table appears to meet this constraint: you could break it down into, e.g., four tables, one mapping the quad id to the IP address, one mapping it to the HELO string, etc. However, each of these would share a unique constraint with the original table, the quad id; hence the original table is in 5NF.

As for 6NF, I don't think the revised schema meets that: 6NF means you can't losslessly decompose the table at all. In the revised schema, the four tables mapping IP id to IP address etc. are in 6NF, but the table mapping quad id to a unique combination of IP id, HELO id, etc. is not: it could be decomposed into four tables similarly to how the original table could be.

(Interestingly, if the original table dropped the quad ID column and just relied on a composite primary key of IP address, HELO string, FROM address and TO address, it would be in 6NF.)

Re: A terrible schema from a clueless programmer

#439

Post by rjzzleep »

hinkley wrote: post #374
dan-robertson wrote: post #328

Earlier quoted context omitted.

Example: if you delete a record from the customers table, you want an index on the foreign key in the orders table to delete the corresponding entries. This also means the performance of the delete can have the unfortunate property where the small work of deleting a single row cascades into the work of deleting many rows.

Which is why many elect to just ban deletes.

That’s not the reason. The reason why people don’t delete is because nobody wants to be left with inconsistent data relations. Deleting a customer is more deleting their PII(our scrambling it) and leaving everything else in tact.

Or in the case of Silicon Valley, leave everything in tact with a disabled flag and then spam you for the next decade or so.

Re: A terrible schema from a clueless programmer

#440

Post by HeavyStorm »

I do think the author still has misconceptions over how a database works, and I'm kind astonished.

No, your "poor" database system won't go through every row when you are querying a varchar column. As long as it is indexed, the database will perform a index scan of some sort. Assume O(logn) as the average complexity for most databases.

Of course, if the data on a column is repeated a lot, normalizing it would bring some advantages, but that would also include performing a join,which isn't cheap.

The key takeaway for me is: let's not let this Rachel person touch databases ever again.

Post reply on HN