Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Mistakes Beginners Make When Working with Databases

craigkerstiens.com

Post reply on HN
Print view View original

171–180 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#171

Post by ryanbrunner »

olavgg wrote: post #155
ryanbrunner wrote: post #132

Earlier quoted context omitted.

The thing is, for most cases relevant to web apps, you don't even actually want binary data. Your application isn't going to touch the file itself anyway - a client somewhere is going to be the one processing and downloading the file. There's never a circumstance where dealing with hundreds of kilobytes or more of data is going to be faster than a 50-character file name, regardless of where it's stored.

Actually in some cases you want your application to touch that blob, especially if want to automatically generate thumbnails and have aggressive/best effort compression on that image. It is the best way to do something about users that uploads a 10MB image that is uncompressed :-)

For sure - but you should ask yourself if you need to routinely deal with that blob, or just process it on the way in? If your upload functionality generates thumbnails and resizes images properly, you can isolate file handling to that section of the code and the rest of your app can just deal with nice, simple URLs.

Re: Mistakes Beginners Make When Working with Databases

#172

Post by dllthomas »

muxxa wrote: post #113
davidw wrote: post #72

Earlier quoted context omitted.

Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course. The second generation of machines, wh…

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is th…

Version 3 and 5 UUIDs are derived from the data. They can be a good fit some places, but you have to be sure the only (pre-hash) collisions you'll have are the collisions you want.

Re: Mistakes Beginners Make When Working with Databases

#173

Post by ryanbrunner »

VLM wrote: post #158
ryanbrunner wrote: post #127

Earlier quoted context omitted.

The problem is there's very few examples of real-world data that actually matches a primary key - i.e. is guaranteed to be unique and never changes. I've been burned by this so many times. In reality, everything needs to at least have some capability to change.

Unique and never changes can be two things concatenated, not one thing. If that concatenation is too long you can ram anything thru a hash to get a constant length smoothly distributed key. Its also fun to use "weak" hashes for this because it trolls wanna be security types who don't understand the application. Given the above, some important things concatenated and hashed works. Can always add an application ID or i…

Concatenating helps you with uniqueness, but not with whether things change (if anything that's part of a composite key changes, it can no longer reliably be part of a key). And it's the changing part that's not realistic with natural keys.

Once you get into application ids / timestamps / whatever you're no longer really using a natural key in my mind, you're just making your own algorithm for a surrogate key.

Re: Mistakes Beginners Make When Working with Databases

#174

Post by collyw »

LunaSea wrote: post #87

The "Over normalization" point is basically saying that SQL is bad at handling relational data. Kind of ironic when you see how much bad press NoSQL databases got for missing join functionalities.

Bad if it is used incorrectly. That could be said of many things.

Re: Mistakes Beginners Make When Working with Databases

#175

Post by mercer »

pietroalbini wrote: post #114
mercer wrote: post #99

Earlier quoted context omitted.

Wait, what? MySQL reuses them? Can you elaborate on that?

That'a bug reported in 2003 and not yet fixed. https://bugs.mysql.com/bug.php?id=199

Oh wow, that's definitely good to know. I do quite a number of migrations and so far this hasn't bitten me, but it could.

Re: Mistakes Beginners Make When Working with Databases

#176

Post by TheCoelacanth »

VLM wrote: post #158
ryanbrunner wrote: post #127

Earlier quoted context omitted.

The problem is there's very few examples of real-world data that actually matches a primary key - i.e. is guaranteed to be unique and never changes. I've been burned by this so many times. In reality, everything needs to at least have some capability to change.

Unique and never changes can be two things concatenated, not one thing. If that concatenation is too long you can ram anything thru a hash to get a constant length smoothly distributed key. Its also fun to use "weak" hashes for this because it trolls wanna be security types who don't understand the application. Given the above, some important things concatenated and hashed works. Can always add an application ID or i…

> So something that's unique is NOW() (assuming low enough sample rate LOL) and something that never changes is a laser serial number, concatenate those and ram thru a hash to make it small and fit.

That sounds very similar to UUID version 1 except that you replaced MAC address with some other identifier and added a hashing step.

Re: Mistakes Beginners Make When Working with Databases

#177

Post by lloyd-christmas »

mathattack wrote: post #163
lloyd-christmas wrote: post #157

Earlier quoted context omitted.

> Many databases also serve multiple applications... Your data store should make sense on its own without being tied to an application's design. While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist…

While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist in the future. Reminds me of the cynical saying for data warehouses, "Data in, but never out." :-) What I have seen frequently enough is myopic…

> The assumptions behind this then gets built into the code base

This logic shouldn't be in the code base. A query should be isolated from the application logic, as I think we can all agree on. A change in the database should only require changing the query/procedure. Your business logic shouldn't be dependent on the internal workings of the query, just on it's input/output which shouldn't need changing. Adding a feature that requires a database refactor shouldn't impact the internal logic of another feature (unless it's intentional).

I'm not encouraging willy-nilly design. I'm not saying "stick everything on one row". Just don't design a one-to-one as a one-to-many just because it might theoretically change. However, you should still have the foresight to put yourself in a position where that change is easy. People seem to think that "refactoring" is a dirty word. I'm reasonably confident none of us have worked on an application that has never been refactored. Plan on those potential refactors, not convince yourself that "this is how proper design works". I find THAT is what inevitably leads to the painful refactors.

> And even if you fix the database, you are missing the historical hierarchies

I'm not following this one. Are you referring to an audit trail?

Re: Mistakes Beginners Make When Working with Databases

#178

Post by brianwawok »

olavgg wrote: post #150
brianwawok wrote: post #144

Earlier quoted context omitted.

So if you do this on a large site, what % of your DB load is going to be serving images? If you put your images behind a CDN, you solve 98% of that.. but you still have a thundering herd problem if your cache gets cold and a bunch of people want to see a non-CDNed iamge. IMHO it is way way way easier to just throw the image up on google cloud storage (or s3), and store the URL in your database. Or better yet, derive…

I use Varnish, so it is never a performance issue.

Does Varnish preload your images from cache?

What if 100 people hit the page at the same time for an uncached image?

I am not saying it can't be done, just having trouble figuring out the benefits.. its easier to store an image in cloud storage over in a database... and cheaper.. and less bug prone...

Re: Mistakes Beginners Make When Working with Databases

#179

Post by hinkley »

_RPM wrote: post #51
hinkley wrote: post #47

Earlier quoted context omitted.

If you can avoid exposing those ID's then 64 bits should do fine with no problems. But it turns out JavaScript can only handle ~54 bit integers, so if you try to randomize your IDs to prevent people scanning your data, you'll be in for a rough patch making sure you always treat IDs as strings. At least with UUID you pretty much have to treat it as a string. You also avoid being the next developer in a long line who t…

The common pattern I've seen is to send the 64 bit integer back as a string. So it would be a "numeric string"

Right, but generally this has not been the default behavior. If you're lucky and your chosen libraries were written by wise people, it's a couple lines of code and a few tests.

But it's usually set wrong by default, and if your IDs are monotonically increasing you'll probably never notice.

Re: Mistakes Beginners Make When Working with Databases

#180

Post by developer2 »

mathattack wrote: post #163
lloyd-christmas wrote: post #157

Earlier quoted context omitted.

> Many databases also serve multiple applications... Your data store should make sense on its own without being tied to an application's design. While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist…

While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist in the future. Reminds me of the cynical saying for data warehouses, "Data in, but never out." :-) What I have seen frequently enough is myopic…

Data warehousing is something I really hate to even look at in general. The implementations are typically optimized not for query performance; instead, they are "designed" to allow people who don't even know what a database is to execute queries.

The idea that an entire database should be designed and normalized purely to allow people with no knowledge of basic database mechanics to work with it shocks me. Instead of designing disgusting database structures, send your marketing analytics people to courses where they can learn the basics. I had one job in particular where I spent more than a year dealing with this crap, and it was the most frustrating thing I've ever had to deal with. Never again.

Post reply on HN