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

131–140 of 493 posts

Re: A terrible schema from a clueless programmer

#131

Post by hn_throwaway_99 »

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

Re: A terrible schema from a clueless programmer

#132

Post by dreyfan »

jugg1es wrote: post #118

One of the most useful lessons I ever learned about designing database schemas was the utter uselessness of indexing and searching on datetime fields. Since virtually every value is going to be different, indexing and searching on that field is (almost) no better than having no index on the datetime field at all. It was a revelation to me when I decided to experiment with having a indexed date-ONLY field and using th…

B-Tree indexes perform just fine on datetime fields. Were you using hash or inverted indexes maybe?

Re: A terrible schema from a clueless programmer

#133

Post by shawnz »

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…

Just because the author was intending to get across a certain point, doesn't mean the implementation details aren't worth discussing too. I don't think many people here would disagree with the central point, so what's there to discuss about it? I think it's uncharitable to assume that everyone talking about the implementation details is "missing the point".

Re: A terrible schema from a clueless programmer

#134

Post by Ginden »

ryukafalz wrote: post #108
tptacek wrote: post #83

Earlier quoted context omitted.

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

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?

> I guess if you ever need to match on a specific prefix storing the numeric IP might be an issue?

If you want to search for range, you can do:

   SELECT INET_NTOA(ip) FROM ips WHERE ip > INET_NTOA(:startOfRange) AND ip

Re: A terrible schema from a clueless programmer

#135

Post by Diggsey »

jugg1es wrote: post #118

One of the most useful lessons I ever learned about designing database schemas was the utter uselessness of indexing and searching on datetime fields. Since virtually every value is going to be different, indexing and searching on that field is (almost) no better than having no index on the datetime field at all. It was a revelation to me when I decided to experiment with having a indexed date-ONLY field and using th…

> Since virtually every value is going to be different, indexing and searching on that field is (almost) no better than having no index on the datetime field at all.

Do you mean exact equality is rarely what you want because most of the values are different?

Or are you talking about the negative effect on index size of having so many distinct values?

I think the latter point could be quite database-dependent, eg. BTree de-duplication support was only added in Postgres 13. However, you could shave off quite a bit just from the fact that storing a date requires less space in the index than a datetime.

Re: A terrible schema from a clueless programmer

#136

Post by hn_throwaway_99 »

jeroenhd wrote: post #94
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…

Based on this piece of old documentation I found [0] for MySQL 3.23 (the most recent version in 2002 as far as I can tell), certain types of indices were only available on certain types of engines. Furthermore, columns were restricted to 255 characters, which may be too short for some of the fields saved in the database. Modern databases abstract away a lot of database complexity for things like indices. It's true th…

The fact remains that whether the text column existed on her original table, or whether it was pulled out to a normalized table, literally all of the same constraints would apply (e.g. max char length, any other underlying limitations of indexing).

The issue is that her analysis of what the issue was with her original table is completely wrong, and it's very weird given that the tone her "present" self is that it's so much more experienced and wise than her "inexperienced, naive" self.

My point is that she should give her inexperience self a break, all that was missing from her original implementation were some indexes.

Re: A terrible schema from a clueless programmer

#137

Post by whoknowswhat11 »

pushrax wrote: post #130
jugg1es wrote: post #118

One of the most useful lessons I ever learned about designing database schemas was the utter uselessness of indexing and searching on datetime fields. Since virtually every value is going to be different, indexing and searching on that field is (almost) no better than having no index on the datetime field at all. It was a revelation to me when I decided to experiment with having a indexed date-ONLY field and using th…

This entirely depends on what kind of index is used. A sorted index, such as a B or B+ tree (used in many SQL databases), will allow for fast point/range lookups in a continuous value space. A typical inverted index or hash based index only allows point lookups of specific values in a discrete value space.

I was just going to make this comment.

Postgres as far as I know uses B-tree by default.

You can switch sort order I think for this as well, so "most recent" becomes more efficient.

Multi-column indexes also work, if you are just searching for first column postgres can still use multi-column index.

Re: A terrible schema from a clueless programmer

#138

Post by globular-toast »

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 real meaning being that people learn and gain experience over time? Is this really something we need to read two pages of text to find out? I think people are justifiably miffed at having read two pages for such a trivial message. Not only that, but these "stupid implementation details" could seriously mislead someone who really is a clueless newbie.

Re: A terrible schema from a clueless programmer

#139

Post by jameshart »

taeric wrote: post #59
havkd wrote: post #53

Earlier quoted context omitted.

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

An index is often easily viewed as another table. So, should. (Some subtleties on projected values and such, but the point stands.)

Yes, the proposed ‘better’ structure basically amounts to building your own indexes. Normally, I’d assume it is better to use the RDBMS’s own engine to do that, don’t roll your own.

There may well be some subtlety to the indexing capabilities of MySQL I’m unaware of though - could easily imagine myself making rookie mistakes like assuming that it has same indexing capabilities. So, to the post’s point - if I were working on a MySQL db I would probably benefit from an old hand’s advice to warn me away from dangerous assumptions.

On the other hand I also remember an extremely experienced MS SQL Server DBA giving me some terrible advice because what he had learned as a best practice on SQL Server 7 turned out to be a great way to not get any of the benefits of a new feature in SQL Server 2005.

Basically, we’re all beginners half the time.

Re: A terrible schema from a clueless programmer

#140

Post by pilif »

ryandrake wrote: post #92
mewpmewp2 wrote: post #78

Earlier quoted context omitted.

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…

Also, the "better" solution assumes IPv4 addresses and is not robust to a sudden requirements change to support IPv6. Best to keep IP address as a string unless you really, REALLY need to do something numerical with one or more of the octets.

Based on my experience I disagree. If there is a native data type that represents your data, you should really use it.

It will make sure that incorrect data is detected at the time of storage rather than at some indeterminate time in the future and it will likely be more efficient than arbitrary strings.

And in case of IP addresses, when you are using Postgres, it comes with an inet type that covers both ipv4 and ipv6, so you will be save from requirement changes

Post reply on HN