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

381–390 of 493 posts

Re: A terrible schema from a clueless programmer

#381

Post by throwaway2037 »

damagednoob wrote: post #261
crispyambulance wrote: post #58

Earlier quoted context omitted.

Yep. Folks are getting lost in the weeds discussing indexing of database tables. That's _totally_ beside the point here. The thing is, the first implementation was a perfectly fine "straight line" approach to solve the problem at hand. One table, a few columns, computers are pretty fast at searching for stuff... why not? In many scenarios, one would never see a problem with that schema. Unfortunately, "operating in a…

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#…

You wrote: >

Do you have a specific example that would help in the case described in Rachel's blog post?

I am still making (and finding) occasional indexing and 3NF mistakes. In my experience, it is always humans finding and fixing these issues.

Re: A terrible schema from a clueless programmer

#382

Post by noduerme »

alksjoflkajsom wrote: post #354
noduerme wrote: post #317

Earlier quoted context omitted.

>>> 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 ha…

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.

Re: A terrible schema from a clueless programmer

#383

Post by ok123456 »

ummonk wrote: post #353
ok123456 wrote: post #253

Earlier quoted context omitted.

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.

A couple times a day at most?

Re: A terrible schema from a clueless programmer

#384

Post by throwaway2077 »

>The rub is that instead of just being slow, it also cost a fair amount of money because this crazy vendor system charged by the row or somesuch. So, by scanning the whole table, they touched all of those rows, and oh hey, massive amounts of money just set ablaze!

nice to read someone smart feeling the same way I do about that kind of shit.

Re: A terrible schema from a clueless programmer

#385

Post by [deleted] »

shrimpx wrote: post #273
welder wrote: post #9

The 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…

> tend to chase off anyone who makes it to the age of 35 That's definitely not true anymore, if it ever was. Senior/staff level engs with 20+ years of experience are sought after and paid a ton of money.

[deleted]

Re: A terrible schema from a clueless programmer

#386

Post by eatonphil »

Aeolun wrote: post #360
path411 wrote: post #276

Earlier quoted context omitted.

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.

In Oracle's proprietary system?

Re: A terrible schema from a clueless programmer

#387

Post by natmaka »

lern_too_spel wrote: post #315
erikerikson wrote: post #156

Earlier quoted context omitted.

The article declared that it was running in production and satisfying the requirement. Do you have a different definition of working? I say that to clarify what I was attempting to communicate by "it worked". I think you're remarking about the relationship between normalized tables and indexes. That relationship does exist in some databases. Please say more if I'm missing your point.

GP's point is that the final schema has a primary key index on the main table that solved the problem, and then it has a bunch of useless additional tables. The solution was just to add a primary key index on the main table. Adding the additional tables just slows down the query because it now has to do five index lookups instead of one.

Using IDs may lead to a performance gain if it leads to a smaller database: less bytes to read/write/cache => less I/O volume, and better cache hits compensating the potentially higher amounts of seeks on mass storage. This is especially true when seeks are quick (SSD...).

Therefore as long as the size data type (C language's "sizeof") used for an ID is inferior to the average size of the column contents then using an ID will very probably lead to a performance gain.

On some DB states and usage patterns (where commonly used data+index cannot fit in RAM: the caches (DB+OS) hit ratios are Creating queries then becomes more difficult (one has to use 'JOIN'), however there are ways alleviate this: using views, "natural join"...

Some modern DB engines let you put data into an index, in order to spare an access to the data when the index is used (Postgresql: see the "INCLUDE" parameter of the "CREATE INDEX"). As far as I understand using a proper ID ( on average smaller than the data it represents) will also lead to a gain(?)

Re: A terrible schema from a clueless programmer

#388

Post by ncmncm »

thaumasiotes wrote: post #372
gonzo41 wrote: post #355

Earlier quoted context omitted.

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

> authk I've always seen authn. Where'd you pick this usage up?

You need to distinguish authentication from authorization. "auth9n" and "auth8n" might be a slight bit too obsure for the purpose.

Re: A terrible schema from a clueless programmer

#389

Post by crispyambulance »

otterley wrote: post #288
crispyambulance wrote: post #286

Earlier quoted context omitted.

> That's where the mentor [...] comes in. The thing is, specifically, that the OP did NOT have a mentor. They had a serious problem to solve, pronto, and knew enough to take a crack at it. OK, the initial implementation was suboptimal. So what? It's totally normal, learn and try again. Repeat. It would be nice if every workplace had a orderly hierarchy of talent where everyone takes care to nurture and support everyo…

Right. Hence my concern about the lack of helpful advice other than “accept yourself”[1]: Neophyte engineers lacking guardrails and local mentors should fall back to what they learned in school: experiment, use your books, and ask questions from the community. Mentors can be found outside the immediate workplace, after all. And when it comes to taking production risks, measure twice and cut once, just like a good car…

> measure twice and cut once

Being able to do that is a luxury that many do not enjoy in nose-to-the-grindstone workplaces. You make an estimate (or someone makes it for you), then you gotta deliver mentor or no mentor, and whether you know the finer points of database design/care-and-feeding or not.

There's something good to be said for taking action. Rachelbythebay did just fine. No one died, the company didn't suffer, it was just a problem, it got corrected later. Big whoop.

Re: A terrible schema from a clueless programmer

#390

Post by fouc »

zmmmmm wrote: post #380

It's clear that she didn't know the first thing about databases. In fact its clear even her current self is pretty naive (almost certainly a few indexes on the first solution would have solved it, or if that was tried and didn't work its a giant hole in the story). She's being quite open and vulnerable here and its important to respect that. So why was the job given to her at the time? Why wasn't there a mentor or le…

The post has a link to an update at the bottom for you if you missed it
Post reply on HN