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

81–90 of 493 posts

Re: A terrible schema from a clueless programmer

#81

Post by themgt »

hn_throwaway_99 wrote: post #11

This post is bizarre, precisely because there is nothing particularly wrong about the original schema, and the author seems to believe that the problem is that the column values were stored as strings, or that the schema wasn't in "third normal form". Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know i…

Also you could just store IPv4 as an unsigned INT directly, rather than strings in a different table and an unsigned INT as a foreign key.

Re: A terrible schema from a clueless programmer

#82

Post by rektide »

The popular advice in these comments to use indexes sounds really good. I haven't thought it out fully, but my first thought was "this sounds like a DIY columnar store". A columnar database does a lot of this work for you. I'm not 100% it's a good option in this case, because things like email subjects are not good columnar data. I would like to know more about how problematic that is.

Re: A terrible schema from a clueless programmer

#83

Post by tptacek »

themgt wrote: post #81
hn_throwaway_99 wrote: post #11

This post is bizarre, precisely because there is nothing particularly wrong about the original schema, and the author seems to believe that the problem is that the column values were stored as strings, or that the schema wasn't in "third normal form". Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know i…

Also you could just store IPv4 as an unsigned INT directly, rather than strings in a different table and an unsigned INT as a foreign key.

I have regretted every single time I've gotten cute about storing IP addresses as scalars in SQL.

Re: A terrible schema from a clueless programmer

#85

Post by erikerikson »

mwcampbell wrote: post #39
gfodor wrote: post #30

Earlier quoted context omitted.

The point is she was being hyper critical of her past schema to make a larger point.

And IIUC, the larger point, made by the twist ending, is that we shouldn't be so critical, but try to actually help newbies learn what we insist that they should know.

It seemed to me the point was that if the industry remains designed to push out experienced engineers that there won't be anyone to mentor the engineers that are just beginning their careers. Further that we will bend the productivity of the field downward and have less effective systems.

Re: A terrible schema from a clueless programmer

#87

Post by throwawayFanta »

I think this blog fails to take into account that 2021 is not 2002. Computer Science is a much more formal/mainstream field of study now, and people don't operate "in a vacuum with no mentorship, guidance, or reference points.”

Some comments on the previous blog post raised important questions regarding minimum understanding/knowledge of technology one utilizes as part of their day job. And I would agree that indexing is a fundamental aspect while using relational databases.

But unfortunately this isn't very uncommon these days, way too often have I heard people in $bigco say, let's use X, everyone uses X without completely understanding the implications/drawbacks/benefits of that choice.

Re: A terrible schema from a clueless programmer

#88

Post by gavinray »

mewpmewp2 wrote: post #78
hn_throwaway_99 wrote: post #11

This post is bizarre, precisely because there is nothing particularly wrong about the original schema, and the author seems to believe that the problem is that the column values were stored as strings, or that the schema wasn't in "third normal form". Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know i…

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 than multiple)

But also yeah I think just a compound UNIQUE constraint on the original columns would have worked

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:

https://en.wikipedia.org/wiki/Database_normalization#Satisfy...

The way I remember 3rd Normal Form is "Every table can stand alone as it's own coherent entity/has no cross-cutting concerns".

So if you have a "product" record, then your table might have "product.name", "product.price", "product.description", etc.

The end schema shown could be described as a "Star Schema" too, I believe (see image on right):

https://en.wikipedia.org/wiki/Star_schema#Example

This Rachel person is also much smarter than I am, and you can make just about anything work, so we're all bikeshedding anyways!

Re: A terrible schema from a clueless programmer

#89

Post by Philip-J-Fry »

maddynator wrote: post #32
ccleve wrote: post #8

Sorry, no. The original schema was correct, and the new one is a mistake. The reason is that the new schema adds a great deal of needless complexity, requires the overhead of foreign keys, and makes it a hassle to change things later. It's better to stick the the original design and add a unique index with key prefix compression , which all major databases do these days. This means that the leading values gets compre…

One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s and key prefix compression thats omnipresent today was likely not that common or potentially not even implemented/available. But i don’t think the point of the post is whats right/wrong way of doing it. The point as mentioned by few here is that programmers makes mistakes. They…

>One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s

Are you implying that isn't the case today? Thousands of (big) companies are still like that and will continue to be like that.

I write my own SQL, design tables and stuff, submit it for a review by someone 10x more qualified than myself, and at the end of the day I'll get a message back from a DBA saying "do this, it's better".

Re: A terrible schema from a clueless programmer

#90

Post by kofejnik »

havkd wrote: post #53
hn_throwaway_99 wrote: post #11

This post is bizarre, precisely because there is nothing particularly wrong about the original schema, and the author seems to believe that the problem is that the column values were stored as strings, or that the schema wasn't in "third normal form". Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know i…

Is this correct? Would indexing the columns instead of moving the values to another table lead to the same increase in performance?

It would be faster, I believe. As we’re only looking for existence, a single index traversal is all io we would need to do
Post reply on HN