The "NoSQL" movement -- which I wisely rejected -- is also responsible for this.
There are problems that nosql databases solve easily that require a lot of fancy tricks in a RDBMS.
A terrible schema from a clueless programmer
121–130 of 493 posts
Re: A terrible schema from a clueless programmer
#122One 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…
You’re maybe thinking of compression? Primary id is unique too but indexed search is still logn compared to full table scan (n)
Re: A terrible schema from a clueless programmer
#123Earlier 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?
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.
Re: A terrible schema from a clueless programmer
#124The 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…
… I appreciate her sentiment at the end there that we should try to change the status quo (which I think does suck), but I'm not sure how much power the average employee has over it. Most employers I've worked at seem loathe to retain anyone past about 2 years. (E.g., currently I'm in the 95'th percentile, after just over 2 years.) IME it takes about 6 months to really learn how a company's systems work to where to proficiency (yes, six months.) which means we're spending ~25% of the time "training", where "training" is usually someone trying something crazy and failing, and getting corrected as opposed to some structured form of learning. Oftentimes the systems that these engineers are stumbling I think are the internal ones; they need more features, or refactoring to account for organic growth, but since they're not shiny new customer facing features they'll get exactly 0 priority from PMs. The engineers who build experience working with these systems and actually know what needs to change and how without breaking the existing use cases … are about to leave the company, since nobody is retained beyond ~2 years.
I also find my own team is usually strapped for resources, normally, people. (Usually politely phrased as "time".) Yes, one has to be wary of mythical man-month'ing it, but like my at my last employ we had essentially 2 of us on a project that could have easily used at least one, if not two more people. Repeat across every project and that employer was understaffed by 50-100%, essentially.
Some company just went for S-1, and they were bleeding cash. But they weren't bleeding it into new ventures: they were bleeding it into marketing. Sure, that might win you one or two customers, but I think you'd make much stronger gains with new products, or less buggy products that don't drive the existing customers away.
Also there's an obsession with "NIT" — not invented there — that really ties my hands as an engineer. Like, everything has to be out sourced to some cloud vendor provider whose product only fits some of the needs and barely, and whose "support" department appears to be unaware of what a computer is. I'm a SWE, let me do my thing, once in a while? (Yes, where there's a good fit for an external product, yes, by all means. But these days my job is 100% support tickets, and like 3% actual engineering.)
Re: A terrible schema from a clueless programmer
#125One 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…
there's more index types than only hashes (which do indeed only support equality)
e.g. btrees allow greater than/less than comparisons
postgres supports these and many more: https://www.postgresql.org/docs/9.5/indexes-types.html
Re: A terrible schema from a clueless programmer
#126This 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…
I especially feel the proposed solution could be problematic given that the data is supposed to be transient. How are keys deleted from the "id | value" tables when rows are deleted from the main table? Does it just keep accumulating rows or is there something like a GC query running occasionally that deletes all unreferenced values?
Re: A terrible schema from a clueless programmer
#127One 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…
Did you also learn that certain indexes allow for range queries, and modern databases are quite efficient at those?
And yes, please write an article, it would be quite interesting. With data and scripts to reproduce, of course.
Re: A terrible schema from a clueless programmer
#128This 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…
Clearly it worked. However... Consider the rows `a b c d e` and `f b h i j`. How many times will `b` be stored in the two formulations? 2 and 1, right? The data volume has a cost. Consider the number of cycles to compare a string versus an integer. A string is, of course, a sequence of numbers. Given the alphabet has a small set of symbols you will necessarily have a character repeated as soon as you store more value…
> Clearly it worked.
No it didn't. Someone else put an index on the main table and that worked.
Re: A terrible schema from a clueless programmer
#129Earlier quoted context omitted.
It seemed to me the point was that if the industry remains designed to push out experienced engineers that there won't be anyone to mentor the engineers that are just beginning their careers. Further that we will bend the productivity of the field downward and have less effective systems.
In what way is the industry designed that way now? Maybe some get sucked into management roles, but I don’t see any evidence of a broad design to push them out.
Role count pyramids, volume and inflexibility of working hours, abusive management techniques, a constant treadmill of the new hotness, more...
Some offices avoid many of the challenging patterns but not the vast majority. A lot of it is natural result of the newness of the industry and the high stakes of investment. Many of the work and organizational model assumptions mean that offering flexibility to employees complicates the already challenging management role. Given the power of those roles, this means workers largely must fit into the mold rather than designing a working relationship that meets all needs. As the needs change predictably over time a mounting pressure develops.
I'm only 17 years in but it has gotten harder and harder to find someone willing to leave me to simply code in peace and quiet and dig my brain into deep and interesting problems.
Re: A terrible schema from a clueless programmer
#130One 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.