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

171–180 of 493 posts

Re: A terrible schema from a clueless programmer

#171

Post by ryukafalz »

soheil wrote: post #123
ryukafalz wrote: post #108

Earlier quoted context omitted.

What issues did you run into? Having admittedly never done this before, it feels like whenever you need the dotted-quad IP you could just query: SELECT INET_NTOA(ip) FROM ips WHERE... ...in MySQL at least. (Which might be why MySQL has this function built in?) I guess if you ever need to match on a specific prefix storing the numeric IP might be an issue?

Because as shocking as this might sound like unlike the characters in the movie Matrix I don't have a built-in INET_NTOA function in my retina to see the string form of the IP addresses when glancing at table rows of a large table looking for patterns or a certain IP.

How often are you looking at the full contents of a table vs. looking at the results of a query that you could throw an INET_NTOA onto?

Re: A terrible schema from a clueless programmer

#172

Post by GuB-42 »

The problem, I think is that most people, me included, don't really know what databases really do. There is a whole lot about optimizing procedural code, with a variety of tools, the dangers of premature optimization and the tradeoff with readability. Anyone with a computer science degree has heard about algorithmic complexity, caches, etc...

But databases are just magic. You try things out, usually involving a CREATE INDEX at some point, and sometime it gets faster, so you keep it.

Rachel, in he blog post is a good example of that thought process. She used a "best practice", added an index (there is always an index) and it made her queries faster, cool. I don't blame her, it works, and it is a good reminder of the 3NF principle. But work like that on procedural code and I'm sure we will get plenty of reactions like "why no profiler?".

Many, many programmers write SQL, but very few seem to know about query plans and the way the underlying data structures work. It almost looks like secret knowledge of the DBA caste or something. I know it is all public knowledge of course, but it is rarely taught, and the little I know about is is all personal curiosity.

Re: A terrible schema from a clueless programmer

#173

Post by fwip »

fullstackchris wrote: post #146

I guess I've come away with a totally different takeaway than most. This post is rather strong on the blame game, which could be fixed by one thing... RTFD! (Read The F**in Docs!) - I only skimmed the post, but its definitely something that would have been avoided had some SQL documentation or introduction been read. I'd argue one of the huge things that differentiates "senior" developers from all the "other" levels…

The problem here is "unknown unknowns," coupled with the real business pressure to get solutions out the door. The MySQL manual is (currently) over 6,000 pages. A developer new to SQL or relational DBs doesn't know how many of those are necessary to read and comprehend to deploy a working solution.

And in this case, she designed and deployed the system, and it worked and met the business needs for several months. When performance became an issue, she optimized. I'm sure she had plenty of other unrelated work to do in the meantime, especially as a lead/solo dev in the early 2000's.

Sounds like a productive developer to me.

Re: A terrible schema from a clueless programmer

#174

Post by omegalulw »

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…

> Sorry, no. The original schema was correct, and the new one is a mistake.

Well, you also save a space by doing this (though presumably you only need to index the emails as IPs are already 128 bits).

But other than that, I'm also not sure why the original schema was bad.

If you were to build individual indexes on all four rows, you would essentially build the four id tables implicitly.

You can calculate the intersection of hits on all four indexs that match your query to get your result. This is linear in the number of hits across all four indexes in the worst case but if you are careful about which index you look at first, you will probably be a lot more efficient, e.g. (from, ip, to, helo).

Even with a multi index on the new schema, how do you search faster than this?

Re: A terrible schema from a clueless programmer

#175

Post by pg_bot »

rmk wrote: post #157

Why not store the IP Address as a 32-bit number (IPv4 addresses)? Why store it as a string in the first place? This is something I did not quite get. Also, wouldn't it be better to split out the domain from the email address for ease of filtering? Also, how does performing joins give a performance advantage here. I'm assuming there would be queries to get at the IDs of at least one, but going up to 4, to get at the I…

You could store the IP as a 32 bit unsigned int. There's no issue with that, but I would probably recommend nowadays to use an internal cidr or inet type if your database supports that. It probably wouldn't be better to split the email address from the domain for filtering since email address formats tend to be a bit tricky.

Joins give a performance advantage due to the fact that you aren't duplicating data unnecessarily. The slow query in question becomes five queries (4 for the data once for the final lookup) which can each be done quickly and if any one of them return nil, you can return a timeout.

Re: A terrible schema from a clueless programmer

#176

Post by dehrmann »

gfodor wrote: post #30
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…

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

I have mixed feelings about this. On one hand, I appreciate the larger point, and pointing out mistakes again goes against that point. On the other, the post had so many technical details it overshadowed the larger point, especially for something that wasn't obviously broken.

Re: A terrible schema from a clueless programmer

#177

Post by empthought »

havocsupreme wrote: post #54

I’m pretty sure all the people who are criticizing the database design haven’t read the ending. The article isn’t about the schema, it’s about helping those entry level programmers

You don’t think it’s helpful for people to steer entry level programmers away from bad ideas like this one?

Re: A terrible schema from a clueless programmer

#179

Post by newaccount74 »

hn_throwaway_99 wrote: post #131
darkwater wrote: post #48

I'd really love to be snarky here but I'll try to be polite: all those comments about the example situation are missing the whole point of the post. And it really worries me that there is a good chunk of the tech workers that just ignores the real meaning of something and just nitpick about stupid implementation details. The post is about managing rookie errors, being empathetic and also warn the ageism that pervades…

The reason "the details are important" here are not because of the nitty gritty around what mistakes a "novice" programmer made. They are important because the present incarnation of the author is making all the wrong diagnoses about the problems with the original implementation, despite doing it with an air of "Yes, younger me was so naive and inexperienced, and present me is savvy and wise".

I think she inadvertently made a different point, which is that even experienced developers sometimes misunderstand the problem and make mistakes.

Or an even better argument: you don't need to actually understand the problem to fix it, often you accidentally fix the problem just by using a different approach.

Post reply on HN