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

471–480 of 493 posts

Re: A terrible schema from a clueless programmer

#471

Post by rk06 »

int_19h wrote: post #425
rk06 wrote: post #423

Earlier quoted context omitted.

that is not true. the sub tables guarantee that value is unique in the entire column. while main table did not have any such guarantees, so, there will be a lot of indexing and duplication in atleast 3 (of 4 sub tables) Moreover, indexing would be much faster and efficient as there are only unique values in sub tables

Why wouldn't it have such guarantees, if the indices in question are defined as UNIQUE?

Because they are not unique in main table.

For eg: if there are multiple emails from same domain name, then domain name column will have same entry multiple times.

When domain is moved to sub table, main table still has duplicate entries, in form of foreign keys, while domain table has unique entry for each domain name

Re: A terrible schema from a clueless programmer

#472

Post by _3u10 »

miyuru wrote: post #431
_3u10 wrote: post #396

Earlier quoted context omitted.

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/

Thanks! I was really surprised to find it myself. I used HN in some sample code for analyzing how SSL tickets are reused and was surprised to see an IPv4 address come back.

Re: A terrible schema from a clueless programmer

#473

Post by int_19h »

rk06 wrote: post #471
int_19h wrote: post #425

Earlier quoted context omitted.

Why wouldn't it have such guarantees, if the indices in question are defined as UNIQUE?

Because they are not unique in main table. For eg: if there are multiple emails from same domain name, then domain name column will have same entry multiple times. When domain is moved to sub table, main table still has duplicate entries, in form of foreign keys, while domain table has unique entry for each domain name

Foreign key columns (i.e. the ones in the main table) are often indexed themselves, and those indices can also be UNIQUE.

Re: A terrible schema from a clueless programmer

#474

Post by wpietri »

ppp3 wrote: post #446
wpietri wrote: post #333

Earlier quoted context omitted.

Could you say more about why that was the culprit and not some problem with the load testing? For the Olympics, it seems like the most important test is "What happens when we launch?" That's the kind of thing I'd run on every commit.

an automated system level upgrade in the production env, exactaly 10 sections before launch. load tests usually only happens on staging env, not production, that is the policy.

In that case, it sounds like a really good reason to question why the staging environment is not enough like prod that load tests are valid. Or why a not-fully-tested system upgrade would happen 10 seconds before launch.

Re: A terrible schema from a clueless programmer

#475

Post by [deleted] »

teh_klev wrote: post #436
ericbarrett wrote: post #245

Earlier quoted context omitted.

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

[deleted]

Re: A terrible schema from a clueless programmer

#476

Post by WorldMaker »

wruza wrote: post #343
ericbarrett wrote: post #245

Earlier quoted context omitted.

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…

indexes take space, and if you index all your text columns you'll balloon your DB … also add write and compute burden for inserts/updates as now the DB engine has to compute and insert new index entries in addition to the row itself. Indexes didn’t go away with these extra tables, they live in the ‘id’ field of each one. They also probably had UNIQUE constraint on the ‘value’ field, spending time on what you describe…

Right, indexes don't "go away" as you normalize, but the amount of data indexed drastically shifts and four unique indexes are pretty much guaranteed to be smaller in size than one massive combinatorial index of (all, four, possible, values). Not just in the case where any duplicates exist, but just alone in terms of the database's overhead in things like field delimiters and page metadata. While the specifics will vary a lot with specific DB implementations, generally with varchars involved you can usually assume a worst-case in terms of field delimiting (and from there sometimes a worst-case in average page size and how that impacts B-Tree balancing).

In general, Indexes alone can't save you from a normalization problems. From certain points of view an Index is itself another form of denormalization and while it is very handy form of denormalization, heavily relying on big composite indexes (and the "natural keys" they represent) makes normalization worse and you have to keep such trade-offs in mind just as you would any other normalization planning.

It is theoretically possible a database could do something much smarter and semi-normalized with for instance Trie-based "search" indexes, but specifically in 2002 so far as I'm aware of most of the databases at the time such indexes were never the default and didn't have great multi-column support and would have been expensive to compute if you did turn them own. Even such "smart" Indexes would likely still have suggested you normalize first.

Re: A terrible schema from a clueless programmer

#477

Post by WorldMaker »

carapace wrote: post #56
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…

(It seems a lot of folks are getting nerd-sniped by the set-up and missing the moral of the story, eh?) I don't know the actual numbers, but it's been pointed out that at any given time something like half of all programmers have been doing it less than five years, for decades now. That, plus the strident ignorance of past art and practice, seem to me to bring on a lot of issues.

The math would resemble xkcd's Lucky Ten Thousand: https://xkcd.com/1053/

With yes the added confounding factors of how often our industry seems to prefer to hire youth over experience, and subsequently how often experience "drains" to other fields/management roles/"dark matter".

Re: A terrible schema from a clueless programmer

#478

Post by WorldMaker »

FreezerburnV wrote: post #458
goto11 wrote: post #433

Earlier quoted context omitted.

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…

Repeatedly concatting a string is the fastest way I am aware of to build a longer one in Javascript. (This has been deliberately optimized for) I believe PHP this might also be the case for, or at least very fast. Perl might be pretty fast at this but I could be wrong.

I know JS's case somewhat well and the lesson there is not the comment such as above that "it just does the right thing". JS doesn't use a "String Builder" under the hood with a lot of string + calls, but instead that JS does subtly the "wrong" thing as an optimization: in the .NET CLR strings are always, always immutable (or they are not strings according to guarantees that the CLR makes). JS lives in a single language VM that makes no such guarantees, and often lives in strictly single threaded worlds where the VM doesn't have to guarantee the immutability of strings to anyone at the binary memory layout level, so most JS VMs are free to do the "wrong thing" (from the .NET CLR perspective) and just mutably alter strings under the hood as a deliberate optimization.

There's a lot of interesting conflations of "right" versus "wrong" in this this thread. There's the C# "best practices" "right" versus "wrong" of knowing when to choose things like StringBuilder over string.Concat or string's + operator overloading (which does just call string.Concat under the hood). There's the "does that right/wrong" apply to other languages question? (The answer is far more complex than just "right" and "wrong".) There's the VM guarantees of .NET CLR's hard line "no string is mutable" setting a somewhat hard "right" versus "wrong" and other language's VMs/interpreters not needing to make such guarantees to themselves or to others. None of these are really ever "right" versus "wrong", almost all of them are trade-offs described as "best practices" and we take the word "best" too strongly especially when we don't know/examine/explore the trade-off context it originated from (and sometimes wrongly assuming that "best" means "universally the best").

Re: A terrible schema from a clueless programmer

#479

Post by Brian_K_White »

twic wrote: post #434
Brian_K_White wrote: post #252

Earlier quoted context omitted.

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.

incorrect

Re: A terrible schema from a clueless programmer

#480

Post by [deleted] »

thaumasiotes wrote: post #403
ncmncm wrote: post #388

Earlier quoted context omitted.

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

What a completely nonresponsive "response". To the best of my knowledge, no one has ever even tried to use auth9n or auth8n. But that's not where the 'n' in authn comes from. authn and authk are both equally distinct from authz.

[deleted]
Post reply on HN