A terrible schema from a clueless programmer • Hacker News Board

Re: A terrible schema from a clueless programmer

#461

Post by dang » Mon, Nov 08, 2021, 4:13 PM UTC

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…

Please make your substantive points without calling names. This is in the site guidelines:

https://news.ycombinator.com/newsguidelines.html

Re: A terrible schema from a clueless programmer

#462

Post by dang » Mon, Nov 08, 2021, 4:14 PM UTC

HeavyStorm wrote: post #440

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

Personal attacks will get you banned here. Please review the site guidelines and make your substantive points without swipes in the future.

https://news.ycombinator.com/newsguidelines.html

Re: A terrible schema from a clueless programmer

#463

Post by ttfkam » Mon, Nov 08, 2021, 4:16 PM UTC

Not terribly convinced by arguments about normalization for normalization's sake. As with most things, the definitive answer is "it depends". It's not the 1990s anymore. https://youtu.be/8Fb5Qgpr03g

Re: A terrible schema from a clueless programmer

#464

Post by stolsvik » Mon, Nov 08, 2021, 4:25 PM UTC

The normalized solution sounds like a performance nightmare?! Stick the original data into something like Clickhouse? (After checking whether indexing would fix it for a few years more)

Re: A terrible schema from a clueless programmer

#465

Post by yobbo » Mon, Nov 08, 2021, 4:51 PM UTC

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…

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

Re: A terrible schema from a clueless programmer

#466

Post by dang » Mon, Nov 08, 2021, 5:13 PM UTC

IceDane wrote: post #47

The moral of this post really falls flat coming from this author, most of whose posts are snarky, passive-aggressive rants where she rants about someone's alleged incompetence or something similar.

Personal attacks are not allowed on HN.

If you'd please review https://news.ycombinator.com/newsguidelines.html and stick to the rules when posting here, we'd appreciate it.

Edit: you've been breaking the site guidelines repeatedly. We ban accounts that do that. I don't want to ban you, so please stop doing this!

Re: A terrible schema from a clueless programmer

#467

Post by tkot » Mon, Nov 08, 2021, 5:23 PM UTC

gavinray wrote: post #299

tkot wrote: post #281

Earlier quoted context omitted.

Would it not prevent some optimizations based on statistics regarding the data distribution, like using the most selective attribute to narrow down the rows that need to be scanned? I'm assuming there are 2 indexes, 1 for each column that gets combined. Let's say you know the lastname (Smith) but only the first letter of the firstname (A) - in the proposed scenario only the first letter of the firstname helps you nar…

I won't pretend to be an expert in this realm, but see here: https://www.postgresql.org/docs/current/textsearch-tables.ht... Specifically, the part starting at the below paragraph, the explanation for which continues to the bottom of the page: "Another approach is to create a separate tsvector column to hold the output of to_tsvector. To keep this column automatically up to date with its source data, use a stored gen…

Oh, so by fuzzy search you mean full text search, not just a simple 'LIKE A%' pattern - it's an entirely different kind of flying altogether (https://www.youtube.com/watch?v=3qNtyfZP8bE).

I don't know what kind of internal representation is used to store tsvector column type in PG, but I think GIN index lookups should be very simple to parallelize and combine (I believe a GIN index would basically be a map [word -> set_of_row_ids_that_contain_the_word] so you could perform them on all indexed columns at the same time and then compute intersection of the results?). But maybe two lookups in the same index could be somehow more efficient than two lookups in different indexes, I don't know.

I'm still sceptical about the LIKE/ILIKE scenario though. "WHERE firstname LIKE 'A%' and lastname LIKE 'Smith' " can easily discard all the Joneses and whatnot before even looking at the firstname column, whereas "WHERE combined_firstname_and_lastname LIKE 'A%SMITH' " will only be able to reject "Andrew Jones" after reading the entire string.

Re: A terrible schema from a clueless programmer

#468

Post by tremon » Mon, Nov 08, 2021, 5:47 PM UTC

rjzzleep wrote: post #439

hinkley wrote: post #374

Earlier quoted context omitted.

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.

I don't buy that reason, because that inconsistency can be easily prevented with good schema hygiene (either ON DELETE NO ACTION or ON DELETE CASCADE). The problem is rather that in order to maintain consistent data relations, delete operations must be carefully designed and that part of the functional design is usually skipped because it's perceived as not important.

Which is more or less what the GP says as well, deletes are not implemented because doing it properly requires proper design.

Re: A terrible schema from a clueless programmer

#469

Post by fatnoah » Mon, Nov 08, 2021, 5:48 PM UTC

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…

I've never had to deal with a production-breaking issue resulting from this, but I have had to debug a collation-related issue in SQL Server that caused table scans to occur. Fortunately, the SQL profiler made it really obvious that a collation mismatch was the culprit.

Re: A terrible schema from a clueless programmer

#470

Post by ufmace » Mon, Nov 08, 2021, 6:44 PM UTC

goto11 wrote: post #433

MarkSweep wrote: post #432

Earlier quoted context omitted.

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

I don't think StringBuilder is faster specifically because it allocates a large buffer. Pretty much every language with growable data structures can already grow any such structure pretty fast, including arrays, maps, and mutable strings. They already have a bunch of pre-set constants about stuff like how big to allocate for the initial empty one and how much more to allocate every time you overflow to balance speed and memory efficiency. It's faster because it's mutable and keeps adding new data to the existing buffer until it gets too large.